Hello all,
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.
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 was failing.
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.
Best Regards,
Jayadevan