From: | Jim Finnerty <jfinnert(at)amazon(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: insensitive collations |
Date: | 2021-04-04 13:11:57 |
Message-ID: | 1617541917530-0.post@n3.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Justin Pryzby | 2021-04-04 13:19:19 | Re: ALTER TABLE ADD COLUMN fast default |
Previous Message | Anton Voloshin | 2021-04-04 12:49:35 | [PATCH] typo fix in collationcmds.c: "if they are distinct" |