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.