public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Teju Jakkidi <[email protected]>
To: pgsql-admin <[email protected]>
Subject: Re: Issue with retrieving data when the column has @- in it's value
Date: Sat, 05 Apr 2025 17:18:42 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAKA2XvYUiaY8f+EzOP4ANLE5g3ni9wAZOFf1r4u_k0YguthZ1Q@mail.gmail.com>
References: <CAKA2XvYUiaY8f+EzOP4ANLE5g3ni9wAZOFf1r4u_k0YguthZ1Q@mail.gmail.com>

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Issue with retrieving data when the column has @- in it's value
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox