Hello!
I seem to not be able to get PostgreSQL to sort rows by a string column respecting the diacritics.
I read [1] that it's possible to define a custom collation having collation strength "ks" set to "level2", which would mean that it's accent-sensitive.
However, when I try to actually sort using that collation, the order seem to be accent-insensitive.
For example:
CREATE TABLE test (string text);
INSERT INTO test VALUES ('bar'), ('bat'), ('bär');
CREATE COLLATION "und1" (provider = icu, deterministic = false, locale = 'und-u-ks-level1');
CREATE COLLATION "und2" (provider = icu, deterministic = false, locale = 'und-u-ks-level2');
CREATE COLLATION "und3" (provider = icu, deterministic = false, locale = 'und-u-ks-level3');
SELECT * FROM test ORDER BY string collate "und1";
SELECT * FROM test ORDER BY string collate "und2";
SELECT * FROM test ORDER BY string collate "und3";
All three collations give me the same order: bar < bär < bat, although an accent-sensitive order would be bar < bat < bär
This does lose "bär", meaning that those strength levels do have some kind of an effect on "DISTINCT":
SELECT DISTINCT string COLLATE "und1" FROM test;
But it's not working on "ORDER BY".
Do I misunderstand the collation capabilities? Is there a way to actually get an accent-sensitive order?
Also, is there a way to see what options are there for the default built-in collations? I don't see, for example, the used "ks" level in the "pg_collation" table data.
Best regards,
Janis
[1]
https://www.postgresql.org/docs/current/collation.html#ICU-COLLATION-COMPARISON-LEVELS
