Message-ID: <dcfcfef4b3f4ed1d0799f36c49d71a1f1c4d0a8c.camel@cybertec.at>
Subject: Re: Sorting by respecting diacritics/accents
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: =?UTF-8?Q?J=C4=81nisE?= <janise@inbox.lv>, 
 "pgsql-general@lists.postgresql.org"
	 <pgsql-general@lists.postgresql.org>
Date: Fri, 25 Jul 2025 16:23:48 +0200
In-Reply-To: <1814742357.238678.1753437917809@w8>
References: <1814742357.238678.1753437917809@w8>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-1.fc42) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/dcfcfef4b3f4ed1d0799f36c49d71a1f1c4d0a8c.camel%40cybertec.at>
Precedence: bulk

On Fri, 2025-07-25 at 13:05 +0300, J=C4=81nisE wrote:
> I seem to not be able to get PostgreSQL to sort rows by a string column r=
especting the diacritics.=20
>=20
> I read [1] that it's possible to define a custom collation having collati=
on strength "ks"
> set to "level2", which would mean that it's accent-sensitive.
>=20
> However, when I try to actually sort using that collation, the order seem=
 to be accent-insensitive.
>=20
> For example:
>=20
> =C2=A0CREATE TABLE test (string text);=20
> =C2=A0INSERT INTO test VALUES ('bar'), ('bat'), ('b=C3=A4r');=20
> =C2=A0CREATE COLLATION "und1" (provider =3D icu, deterministic =3D false,=
 locale =3D 'und-u-ks-level1');=20
> =C2=A0CREATE COLLATION "und2" (provider =3D icu, deterministic =3D false,=
 locale =3D 'und-u-ks-level2');=20
> =C2=A0CREATE COLLATION "und3" (provider =3D icu, deterministic =3D false,=
 locale =3D 'und-u-ks-level3');=20
> =C2=A0SELECT * FROM test ORDER BY string collate "und1";=20
> =C2=A0SELECT * FROM test ORDER BY string collate "und2";=20
> =C2=A0SELECT * FROM test ORDER BY string collate "und3";
>=20
> All three collations give me the same order: bar < b=C3=A4r < bat, althou=
gh an accent-sensitive
> order would be bar < bat < b=C3=A4r
>=20
> This does lose "b=C3=A4r", meaning that those strength levels do have som=
e kind of an effect on "DISTINCT":=20
> SELECT DISTINCT string COLLATE "und1" FROM test;
>=20
> But it's not working on "ORDER BY".
>=20
> Do I misunderstand the collation capabilities? Is there a way to actually=
 get an accent-sensitive order?

Yes, I thing you misunderstand what "accent sensitive" means.
It means that 'bar' <> 'b=C3=A4r'.

Natural language collations compare strings on different levels:
- 'bar' and 'b=C3=A4r' are identical on the first level (base character)
- 'bar' and 'b=C3=A4r' are different on the second level (accent)
- there are two more levels, the third being case

Strings are ordered by the first level first, then by the second, and so on=
.

I recommend reading Peter's excellent blog:
http://peter.eisentraut.org/blog/2023/05/16/overview-of-icu-collation-setti=
ngs

So you end up with 'bar' < 'b=C3=A4r' < 'bat', because the first two compar=
e
equal on level 1.

What you are looking for is a collation where accents are a first-level
difference.  The only way to do that with ICU collations, as far as I know,
is to add explicit rules, like in this example:
https://stackoverflow.com/a/77288282/6464308

> 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.=20

You can see that in the "colllocale" column.  The name of the ICU locale
determines its capabilities.

Yours,
Laurenz Albe