From: Jim Finnerty <jfinnert@amazon.com>
To: pgsql-hackers@postgresql.org
Subject: Re: insensitive collations
Date: Sat, 3 Apr 2021 11:47:46 -0700 (MST)
Message-ID: <1617475666203-0.post@n3.nabble.com> (raw)
In-Reply-To: <1616677789675-0.post@n3.nabble.com>
References: <5c7f51b0-6788-4ffd-b9c4-23b710373140@manitou-mail.org>
<fd54ad77-fb1a-c77d-1371-d7850562b080@2ndquadrant.com>
<440ec733-d068-46d3-9756-f5f692aba7c8@manitou-mail.org>
<99818c53-d10f-eb0b-bcb1-fd5d60f6dd96@2ndquadrant.com>
<f2b3b8e8-12c4-ceb7-6803-b4af6eb2cb71@2ndquadrant.com>
<693ad06d-db9a-4e59-8131-f823483c5893@manitou-mail.org>
<1616528764665-0.post@n3.nabble.com>
<1616618498263-0.post@n3.nabble.com>
<54ef5bfe-043e-4328-9d70-5818789838f4@manitou-mail.org>
<1616677789675-0.post@n3.nabble.com>
My previous post had a flaw, but fixing that flaw led me to what may be a
bug?
Using column COLLATE "C" ILIKE pattern almost does what you'd like, but the
single-character wildcard is treated as a single byte with "C", and that
won't match a multi-byte character. The fix, I thought, would be to use a
deterministic CS_AS ICU collation, since we can handle the per-character
advance correctly in that case (we think). Well, maybe not. It looks like
single-character wildcards using a deterministic ICU collation match a
single byte instead of a single character:
This creates a deterministic case-sensitive, accent-sensitive collation in a
utf8-encoded database:
SET client_encoding = WIN1252;
CREATE COLLATION CS_AS (
provider = icu,
locale = 'utf8@colStrength=secondary;colCaseLevel=yes',
deterministic = true);
CREATE TABLE locations (location VARCHAR(255) COLLATE CS_AS);
CREATE INDEX location_index ON locations (location);
INSERT INTO locations VALUES ('Franche-Comté')
, ('Midi-Pyrénées')
, ('midi-Pyrénées')
, ('midi-Pyrenées')
, ('Brian Bruß')
, ('Brian Bruss')
, ('Steven Sossmix')
, ('Provence-Alpes-Côte d Azur');
postgres=# SELECT * FROM locations WHERE location LIKE 'Franche-Comté';
location
----------------
Franche-Comté
(1 row)
postgres=# SELECT * FROM locations WHERE location LIKE 'Franche-Comt_'; --
is this a bug?
location
----------
(0 rows)
postgres=# SELECT * FROM locations WHERE location LIKE 'Franche-Comt__'; --
the wildcard is applied byte by byte instead of character by character, so
the 2-byte accented character is matched only by 2 '_'s
location
----------------
Franche-Comté
(1 row)
-----
Jim Finnerty, AWS, Amazon Aurora PostgreSQL
--
Sent from: https://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html
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-hackers@postgresql.org
Cc: jfinnert@amazon.com
Subject: Re: insensitive collations
In-Reply-To: <1617475666203-0.post@n3.nabble.com>
* 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