BUG #19425: Parametric settings in collation not working in rule syntax

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: dorian(dot)752(at)live(dot)fr
Subject: BUG #19425: Parametric settings in collation not working in rule syntax
Date: 2026-03-04 08:43:13
Message-ID: 19425-58915e19dacd4f40@postgresql.org
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 19425
Logged by: Dorian
Email address: dorian(dot)752(at)live(dot)fr
PostgreSQL version: 18.3
Operating system: Windows
Description:

Hello,

I want to create a custom collation with settings and tailoring rules, but I
ran into an issue.

When I create a custom collation, I first used collation settings in the
local, giving me something like this :
CREATE COLLATION IF NOT EXISTS und_ci_ai1(
provider = icu,
locale = 'und-u-ka-shifted-kc-false-ks-level1',
deterministic = false
);

For instance, I can do this request and I will get the result '1' :
SELECT 1 WHERE 'adm' LIKE 'Adm' COLLATE und_ci_ai1;

But, I also want to add a tailoring rule. For the simplicity we will say
that I want to add 'p < q'
CREATE COLLATION IF NOT EXISTS und_ci_ai2(
provider = icu,
locale = 'und-u-ka-shifted-kc-false-ks-level1',
deterministic = false,
rules = $$
& 'p' < 'q'
$$
);

But, and this is my problem, the previous request doesn't seem to work
anymore. It doesn't return '1' :
SELECT 1 WHERE 'adm' LIKE 'Adm' COLLATE und_ci_ai2;

For this request to return '1', I have to add 'a' = 'A' like this :
rules = $$
& 'p' < 'q'
& 'a' = 'A'
$$

I didn't see in the documentation that I can't use settings in locale AND
tailoring rules at the same time. But just in case I tried to use parametric
settings in the rules syntax.
Unfortunately, it doesn't seem to work at all, the request still doesn't
return '1' :
CREATE COLLATION IF NOT EXISTS und_ci_ai3(
provider = icu,
locale = 'und',
deterministic = false,
rules = $$
[strength 1]
[caseLevel off]
$$
);

SELECT 1 WHERE 'adm' LIKE 'Adm' COLLATE und_ci_ai3;

I hope I gave you all the infos you need. Thank you for your time.

Here the documentations I used :
https://www.postgresql.org/docs/current/collation.html#ICU-COLLATION-SETTINGS
https://www.unicode.org/reports/tr35/tr35-collation.html#Setting_Options
https://unicode-org.github.io/icu/userguide/collation/customization/

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Björn Kautler 2026-03-04 10:46:12 GROUP BY in CTE causes ELSE in outer query to be prematurely evaluated
Previous Message Richard Guo 2026-03-04 06:35:56 Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor>