pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Jim Finnerty <jfinnert@amazon.com>
To: pgsql-hackers@postgresql.org
Subject: Re: insensitive collations
Date: Sun, 4 Apr 2021 06:11:57 -0700 (MST)
Message-ID: <1617541917530-0.post@n3.nabble.com> (raw)
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>

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





view thread (44+ messages)

Message-ID: <1617541917530-0.post@n3.nabble.com>
Permalink:  ../1617541917530-0.post@n3.nabble.com/
Also on:    postgresql.org/message-id/1617541917530-0.post@n3.nabble.com

 · 

reply
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: <1617541917530-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