Re: Issue with retrieving data when the column has @- in it's value

On Fri, 2025-04-04 at 18:41 -0700, Teju Jakkidi wrote:
> I have an issue where in the same scenario below works on my POC but is having an issue in the original db.
>
> Original db - OS : Red Hat   (postgres13)
> POC db - OS : SUSE    (postgres 14)
>
> SELECT char_length("col1"), octet_length("col1"), "col1"
> FROM "test_col" WHERE "col1" = 'test_level@-1';
>
> The above query returns data in POC, however, on orig db, it does not return any data.
>
> On orig db, I have to either a dd trim or COLLATE "C" for it to return data.
>
> The collation is same on both the dbs (en_US.UTF-8) and the os locate is also the same on both.
>
> Column col1 datatype is varchar.
>
> I tried checking if there are any spaces or special characters and did not see anything in the output. 
>
> SELECT char_length("col1"), octet_length("col1"), length("col1"), encode("col1"::bytea, 'escape')
> FROM "test_col" WHERE "col1" = 'test_level@-1';
>
> output is the same for both:
>  char_length | octet_length | length |    encode
> -------------+--------------+--------+---------------
>           13 |           13 |     13 | test_level@-1
>
> Can anyone guide me on what else can be checked?

Find out the primary key of the row in question and look what the bytes are:

   SELECT convert_to(col1, 'UTF8') FROM test_col WHERE pkey = 42;

Compare that on both databases, and compare it with the string literal:

   SELECT convert_to('test_level@-1', 'UTF8') FROM test_col WHERE pkey = 42;

Also, compare the collations shown in the \l output for the database.

Yours,
Laurenz Albe