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