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
