Date: Fri, 25 Jul 2025 13:05:17 +0300 (EEST)
From: =?UTF-8?Q?J=C4=81nisE?= <janise@inbox.lv>
To: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Message-ID: <1814742357.238678.1753437917809@w8>
Subject: Sorting by respecting diacritics/accents
MIME-Version: 1.0
Content-Type: multipart/alternative; 
	boundary="----=_Part_238677_595599243.1753437917809"
Archived-At: <https://www.postgresql.org/message-id/1814742357.238678.1753437917809%40w8>
Precedence: bulk

------=_Part_238677_595599243.1753437917809
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Hello! I seem to not be able to get PostgreSQL to sort rows by a string col=
umn respecting the diacritics. I read [1] that it's possible to define a cu=
stom 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: CREA=
TE TABLE test (string text); INSERT INTO test VALUES ('bar'), ('bat'), ('b=
=C3=A4r'); CREATE COLLATION "und1" (provider =3D icu, deterministic =3D fal=
se, locale =3D 'und-u-ks-level1'); CREATE COLLATION "und2" (provider =3D ic=
u, deterministic =3D false, locale =3D 'und-u-ks-level2'); CREATE COLLATION=
 "und3" (provider =3D icu, deterministic =3D false, locale =3D 'und-u-ks-le=
vel3'); SELECT * FROM test ORDER BY string collate "und1"; SELECT * FROM te=
st ORDER BY string collate "und2"; SELECT * FROM test ORDER BY string colla=
te "und3"; All three collations give me the same order: bar < b=C3=A4r < ba=
t, although an accent-sensitive order would be bar < bat < b=C3=A4r This do=
es lose "b=C3=A4r", 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 capabi=
lities? 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 collatio=
ns? I don't see, for example, the used "ks" level in the "pg_collation" tab=
le data. Best regards, Janis [1]=C2=A0 https://www.postgresql.org/docs/curr=
ent/collation.html#ICU-COLLATION-COMPARISON-LEVELS
------=_Part_238677_595599243.1753437917809
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<html>
 <head></head>
 <body>
  Hello!
  <br>=20
  <br> I seem to not be able to get PostgreSQL to sort rows by a string col=
umn respecting the diacritics.
  <br>=20
  <br> I read [1] that it's possible to define a custom collation having co=
llation strength "ks" set to "level2", which would mean that it's accent-se=
nsitive.
  <br>=20
  <br> However, when I try to actually sort using that collation, the order=
 seem to be accent-insensitive.
  <br>=20
  <br> For example:
  <br>=20
  <br> CREATE TABLE test (string text);
  <br> INSERT INTO test VALUES ('bar'), ('bat'), ('b=C3=A4r');
  <br> CREATE COLLATION "und1" (provider =3D icu, deterministic =3D false, =
locale =3D 'und-u-ks-level1');
  <br> CREATE COLLATION "und2" (provider =3D icu, deterministic =3D false, =
locale =3D 'und-u-ks-level2');
  <br> CREATE COLLATION "und3" (provider =3D icu, deterministic =3D false, =
locale =3D 'und-u-ks-level3');
  <br> SELECT * FROM test ORDER BY string collate "und1";
  <br> SELECT * FROM test ORDER BY string collate "und2";
  <br> SELECT * FROM test ORDER BY string collate "und3";
  <br>=20
  <br> All three collations give me the same order: bar &lt; b=C3=A4r &lt; =
bat, although an accent-sensitive order would be bar &lt; bat &lt; b=C3=A4r
  <br>=20
  <br> This does lose "b=C3=A4r", meaning that those strength levels do hav=
e some kind of an effect on "DISTINCT":
  <br> SELECT DISTINCT string COLLATE "und1" FROM test;
  <br>=20
  <br> But it's not working on "ORDER BY".
  <br>=20
  <br> Do I misunderstand the collation capabilities? Is there a way to act=
ually get an accent-sensitive order?
  <br>=20
  <br> Also, is there a way to see what options are there for the default b=
uilt-in collations? I don't see, for example, the used "ks" level in the "p=
g_collation" table data.
  <br>=20
  <br> Best regards,
  <br> Janis
  <br>=20
  <br> [1]&nbsp;
  <a href=3D"https://www.postgresql.org/docs/current/collation.html#ICU-COL=
LATION-COMPARISON-LEVELS" target=3D"_blank" rel=3D"noopener noreferrer">htt=
ps://www.postgresql.org/docs/current/collation.html#ICU-COLLATION-COMPARISO=
N-LEVELS</a>
 </body>
</html>
------=_Part_238677_595599243.1753437917809--