Re: Postgresql 12.9 = Operator issue

Hi,

On Fri, Feb 18, 2022 at 02:59:18PM +0000, Satish Nayak [NMC - IT] wrote:
> 
> After upgrade to postgresql 12.9 from 12.6 , some of the statements having
> ”=” operator not returning any rows..
> Eg :
> 
>  PostgreSQL 12.9 (Ubuntu 12.9-2.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04)
9.3.0,64-bit
 
> => select * from store_item_details  where medicine_name ='DEXTROSE 10% :500 ML';
> (0 rows)
> 
> Where as in 12.6 version , we are able to see the row.
> 
> version | PostgreSQL 12.6 (Ubuntu 12.6-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
5.4.0-6ubuntu1~16.04.12)5.4.0 20160609, 64-bit
 
> hmsuat=# select * from store_item_details  where medicine_name ='DEXTROSE 10% :500 ML';
> hmsuat=# select count(1) from store_item_details  where medicine_name ='DEXTROSE 10% :500 ML';
>  count
> -------
>      1
> (1 row)
> 
> Request your help to know wtr this is a bug or not. If bug , is there any work around ?

Did you also upgrade your system at the same time?  It you upgraded the lib or
ICU library you can definitely corrupt your indexes, see for instance
https://wiki.postgresql.org/wiki/Locale_data_changes or
https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade.html.

A REINDEX of all indexes on collatable datatype would fix the problem.