Collation with upper and numeric comparing in unexpected way - Mailing list pgsql-general

From Matt Magoffin
Subject Collation with upper and numeric comparing in unexpected way
Date
Msg-id 9EBA273E-7F3C-40F4-8156-745A4BB5B090@msqr.us
Whole thread Raw
Responses Re: Collation with upper and numeric comparing in unexpected way
List 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
toachieve that? I’m trying to match the sorting behaviour in external application code. 

Thanks for any help,
Matt





pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: pg_trgm upgrade to 1.6 led to load average increase
Next
From: dmurvihill@gmail.com
Date:
Subject: Re: Request for cryptographic mechanisms used in PostgreSQL