Thread: String comparison fails for some cases after migration

Hi,

On Wed, Oct 27, 2021 at 2:12 PM Jayadevan M <maymala.jayadevan@gmail.com> wrote:
>
> We moved our PostgreSQL database from one hosting provider to another using pgbackrest.  In the new environment, some
comparisonoperations  were failing.  The issue was fixed by running an update. But I am trying to find out what would
havehappened.
 
>
> select * from accounts where email = 'someemail@gmail.com'; -- failed for some email ids even though there were
records.
>
> select * from accounts where lower(trim(email)) <> email; -- fetched no records.
>
> select * from accounts where email::bytea = 'someemail@gmail.com'::bytea; - worked for those records where comparison
wasfailing.
 
>
> update accounts set email = trim(lower(email)); -- fixed the issue.
>
> Source database was PG 13.2, target 13.3.
>
> Any suggestions will be appreciated - not sure if other tables/columns are affected.

Most likely you had a different version of the glibc or ICU libraries
on the new system, which lead to your indexes on collatable datatypes
partially corrupted.  See https://wiki.postgresql.org/wiki/Collations
for more details.

You can issue a REINDEX for each impacted index, or a database-wide REINDEX.

Jayadevan M <maymala.jayadevan@gmail.com> writes:
> We moved our PostgreSQL database from one hosting provider to another using
> pgbackrest.  In the new environment, some comparison operations  were
> failing.  The issue was fixed by running an update. But I am trying to find
> out what would have happened.

Did the underlying OS version change?  If so, see

https://wiki.postgresql.org/wiki/Locale_data_changes

            regards, tom lane