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
