Message-ID: <bf0ae6506619caad2fe176f37cc08b4d370173f8.camel@cybertec.at>
Subject: Re: Issue with retrieving data when the column has @- in it's value
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Teju Jakkidi <teja.jakkidi05@gmail.com>, pgsql-admin
	 <pgsql-admin@lists.postgresql.org>
Date: Sat, 05 Apr 2025 17:18:42 +0200
In-Reply-To: 
 <CAKA2XvYUiaY8f+EzOP4ANLE5g3ni9wAZOFf1r4u_k0YguthZ1Q@mail.gmail.com>
References: 
	<CAKA2XvYUiaY8f+EzOP4ANLE5g3ni9wAZOFf1r4u_k0YguthZ1Q@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/bf0ae6506619caad2fe176f37cc08b4d370173f8.camel%40cybertec.at>
Precedence: bulk

On Fri, 2025-04-04 at 18:41 -0700, Teju Jakkidi wrote:
> I have an issue where in the same scenario=C2=A0below works on my POC=C2=
=A0but is=C2=A0having an issue in the original db.
>=20
> Original db - OS :=C2=A0Red Hat=C2=A0 =C2=A0(postgres13)
> POC db - OS : SUSE=C2=A0 =C2=A0 (postgres 14)
>=20
> SELECT char_length("col1"), octet_length("col1"), "col1"
> FROM "test_col"=C2=A0WHERE=C2=A0"col1" =3D 'test_level@-1';
>=20
> The above query returns data in POC, however, on orig db, it does not ret=
urn any data.
>=20
> On orig db, I have to either a dd trim or COLLATE "C" for it to return da=
ta.
>=20
> The collation=C2=A0is same on both the dbs (en_US.UTF-8) and the os locat=
e is also the same on both.
>=20
> Column col1 datatype is varchar.
>=20
> I tried checking if there are any spaces or special=C2=A0characters and d=
id not see anything in the output.=C2=A0
>=20
> SELECT char_length("col1"), octet_length("col1"), length("col1"), encode(=
"col1"::bytea, 'escape')
> FROM "test_col"=C2=A0WHERE "col1" =3D 'test_level@-1';
>=20
> output is the same for both:
> =C2=A0char_length | octet_length | length | =C2=A0 =C2=A0encode
> -------------+--------------+--------+---------------
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 13 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 13 | =C2=A0 =C2=A0 13 | test_level@-1
>=20
> 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 =3D 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 =3D =
42;

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

Yours,
Laurenz Albe