From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: todd.lang@d2l.com
Subject: BUG #19045: Applying custom collation rules appears to erase existing rules
Date: Tue, 09 Sep 2025 15:13:41 +0000
Message-ID: <19045-eef31bf1c807b524@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 19045
Logged by: Todd Lang
Email address: todd.lang@d2l.com
PostgreSQL version: 17.6
Operating system: Windows 10 64 bit
Description:
Setting up a collation on a table with the following:
DROP TABLE IF EXISTS test_table;
DROP COLLATION IF EXISTS CI_AS;
CREATE COLLATION CI_AS (PROVIDER=icu, LOCALE='en-US-u-ks-level2',
DETERMINISTIC=false);
CREATE TABLE test_table (field1 varchar(256) COLLATE CI_AS);
INSERT INTO test_table VALUES (U&'this is a string.');
INSERT INTO test_table VALUES (U&'THIS IS A STRING.');
Then issue the query:
SELECT * FROM test_table WHERE field1 = 'This is a string.';
This should provide:
"this is a string."
"THIS IS A STRING."
Now alter the collation slightly to include rules. (Note the CREATE
COLLATION line)
DROP TABLE IF EXISTS test_table;
DROP COLLATION IF EXISTS CI_AS;
CREATE COLLATION CI_AS (PROVIDER=icu, LOCALE='en-US-u-ks-level2',
DETERMINISTIC=false, rules='');
CREATE TABLE test_table (field1 varchar(256) COLLATE CI_AS);
INSERT INTO test_table VALUES (U&'this is a string.');
INSERT INTO test_table VALUES (U&'THIS IS A STRING.');
Now issue:
SELECT * FROM test_table WHERE field1 = 'This is a string.';
There are no results.
From the documentation it seems that any text supplied should be additional
rules to the standard rules.
In `pg_locale_icu.c` in the `make_icu_collator` method at line 455, it seems
that it does a simple:
u_strcpy(all_rules, std_rules);
u_strcat(all_rules, my_rules);
which seems like, with the above change, should just append nothing to the
standard rules, causing no change. This, however, is not the case.
I have tried it with various permutations of the `rules`, and while any
rules supplied during the CREATE COLLATION call appear to function, it seems
that all standard rules are forgotten when this option is utilized.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, todd.lang@d2l.com
Subject: Re: BUG #19045: Applying custom collation rules appears to erase existing rules
In-Reply-To: <19045-eef31bf1c807b524@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox