Content-Type: text/plain; charset="iso-8859-15"
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Subject: Re: Support LIKE with nondeterministic collations
From: "Daniel Verite" <daniel@manitou-mail.org>
To: "Peter Eisentraut" <peter@eisentraut.org>
Cc: "pgsql-hackers" <pgsql-hackers@postgresql.org>
In-Reply-To: <700d2e86-bf75-4607-9cf2-f5b7802f6e88@eisentraut.org>
Date: Tue, 30 Apr 2024 14:39:11 +0200
Message-Id: <a827cf68-608e-45cf-9b94-e46f5e9d4171@manitou-mail.org>
Archived-At: <https://www.postgresql.org/message-id/a827cf68-608e-45cf-9b94-e46f5e9d4171%40manitou-mail.org>
Precedence: bulk

	Peter Eisentraut wrote:

> This patch adds support for using LIKE with nondeterministic
>  collations.  So you can do things such as
>=20
>     col LIKE 'foo%' COLLATE case_insensitive

Nice!

> The pattern is partitioned into substrings at wildcard characters
> (so 'foo%bar' is partitioned into 'foo', '%', 'bar') and then then
> whole predicate matches if a match can be found for each partition
> under the applicable collation

Trying with a collation that ignores punctuation:

  postgres=3D# CREATE COLLATION "ign_punct" (
    provider =3D 'icu',
    locale=3D'und-u-ka-shifted',
    deterministic =3D false
  );

  postgres=3D# SELECT '.foo.' like 'foo' COLLATE ign_punct;
   ?column?=20
  ----------
   t
  (1 row)

  postgres=3D# SELECT '.foo.' like 'f_o' COLLATE ign_punct;
   ?column?=20
  ----------
   t
  (1 row)

  postgres=3D# SELECT '.foo.' like '_oo' COLLATE ign_punct;
   ?column?=20
  ----------
   f
  (1 row)

The first two results look fine, but the next one is inconsistent.


Best regards,
--=20
Daniel V=E9rit=E9
https://postgresql.verite.pro/
Twitter: @DanielVerite