Re: insensitive collations

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:

SELECT * FROM locations WHERE location LIKE 'midi-Pyr_n%ées';  -- 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 the
desired CI_AS behavior:
-- note that the single-character wildcard '_' matches either e or é, as
expected

SELECT * FROM locations WHERE location COLLATE SQL_Latin1_General_CP1_CS_AS
ILIKE 'midi-Pyr_n%ées';
   location
---------------
 Midi-Pyrénées
 midi-Pyrénées
 midi-Pyrenées
(3 rows)

EXPLAIN SELECT * FROM locations WHERE location COLLATE
SQL_Latin1_General_CP1_CS_AS ILIKE 'midi-Pyr_n%ées';
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Bitmap Heap Scan on locations  (cost=8.90..20.77 rows=1 width=516)
   Filter: ((location)::text ~~* 'midi-Pyr_n%ées'::text)
   ->  Bitmap Index Scan on location_index  (cost=0.00..8.89 rows=150
width=0)
(3 rows)



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