Date: Sun, 4 Apr 2021 06:11:57 -0700 (MST)
From: Jim Finnerty <jfinnert@amazon.com>
To: pgsql-hackers@postgresql.org
Message-ID: <1617541917530-0.post@n3.nabble.com>
In-Reply-To: <3edec684-85a8-40b7-a47c-16c54d6eb54f@manitou-mail.org>
References: <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> <1617475666203-0.post@n3.nabble.com>
 <3edec684-85a8-40b7-a47c-16c54d6eb54f@manitou-mail.org>
Subject: Re: insensitive collations
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/1617541917530-0.post%40n3.nabble.com>
Precedence: bulk

you were exactly right. With client_encoding set to UTF8 it now works
correctly.

the transformation to do the LIKE or ILIKE on a nondeterministic ICU
collation CI_AS for which there is an otherwise identical deterministic
collation CS_AS is as shown below:=20

SELECT * FROM locations WHERE location LIKE 'midi-Pyr_n%=C3=A9es';  -- what=
 we
want to do
ERROR:  nondeterministic collations are not supported for LIKE

-- explicitly collate with the CS_AS collation and then use ILIKE to get th=
e
desired CI_AS behavior:
-- note that the single-character wildcard '_' matches either e or =C3=A9, =
as
expected

SELECT * FROM locations WHERE location COLLATE SQL_Latin1_General_CP1_CS_AS
ILIKE 'midi-Pyr_n%=C3=A9es';
   location   =20
---------------
 Midi-Pyr=C3=A9n=C3=A9es
 midi-Pyr=C3=A9n=C3=A9es
 midi-Pyren=C3=A9es
(3 rows)

EXPLAIN SELECT * FROM locations WHERE location COLLATE
SQL_Latin1_General_CP1_CS_AS ILIKE 'midi-Pyr_n%=C3=A9es';
                                  QUERY PLAN                               =
   =20
---------------------------------------------------------------------------=
----
 Bitmap Heap Scan on locations  (cost=3D8.90..20.77 rows=3D1 width=3D516)
   Filter: ((location)::text ~~* 'midi-Pyr_n%=C3=A9es'::text)
   ->  Bitmap Index Scan on location_index  (cost=3D0.00..8.89 rows=3D150
width=3D0)
(3 rows)


-----
Jim Finnerty, AWS, Amazon Aurora PostgreSQL
--
Sent from: https://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.h=
tml