| 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
| 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 |