From: Matt Magoffin <postgresql.org@msqr.us>
Content-Type: text/plain;
	charset=utf-8
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.700.81.1.4\))
Subject: Collation with upper and numeric comparing in unexpected way
Message-Id: <9EBA273E-7F3C-40F4-8156-745A4BB5B090@msqr.us>
Date: Wed, 21 Jan 2026 07:36:42 +1300
To: pgsql-general@lists.postgresql.org
Archived-At: <https://www.postgresql.org/message-id/9EBA273E-7F3C-40F4-8156-745A4BB5B090%40msqr.us>
Precedence: bulk

I am using Postgres 17 and trying to configure a collation that sorts =
upper case before lower case and includes numeric sorting:

CREATE COLLATION testsort (provider =3D icu, locale =3D =
'und-u-kf-upper-kn=E2=80=99);

These comparisons are working as I expected:

SELECT 'id-45' < 'id-123' COLLATE testsort; -- true (45 before 123)

SELECT 'id' < 'ID' COLLATE testsort; -- false (upper case before lower =
case)

However combining them resulted in an unexpected result:

SELECT 'id-45' < 'ID-123' COLLATE testsort; -- true

I thought that last one would be false because =E2=80=9CID=E2=80=9D =
would come before =E2=80=9Cid=E2=80=9D. Is there a way to configure the =
collation to achieve that? I=E2=80=99m trying to match the sorting =
behaviour in external application code.

Thanks for any help,
Matt