Collation with upper and numeric comparing in unexpected way

From: Matt Magoffin <postgresql(dot)org(at)msqr(dot)us>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Collation with upper and numeric comparing in unexpected way
Date: 2026-01-20 18:36:42
Message-ID: 9EBA273E-7F3C-40F4-8156-745A4BB5B090@msqr.us
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 = icu, locale = 'und-u-kf-upper-kn’);

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 “ID” would come before “id”. Is there a way to configure the collation to achieve that? I’m trying to match the sorting behaviour in external application code.

Thanks for any help,
Matt

Responses

Browse pgsql-general by date

  From Date Subject
Next Message dmurvihill 2026-01-20 19:03:25 Re: Request for cryptographic mechanisms used in PostgreSQL
Previous Message Adrian Klaver 2026-01-20 16:34:17 Re: pg_trgm upgrade to 1.6 led to load average increase