Re: unicode match normal forms

    Hamann W wrote:

> in unicode letter ä exists in two versions - linux and windows use a
> composite whereas macos prefers
> the decomposed form. Is there any way to make a semi-exact match that
> accepts both variants?

Aside from normalizing the strings into the same normal form
before comparing, non-deterministic ICU collations will recognize them as
identical (they're "canonically equivalent" in Unicode terms)

For instance,

CREATE COLLATION nd (
   provider = 'icu',
   locale='',
   deterministic = false
);

SELECT
 nfc_form,
 nfd_form,
 nfc_form = nfd_form COLLATE nd AS equal1,
 nfc_form = nfd_form COLLATE "C" AS equal2 -- or any deterministic collation
FROM
  (VALUES
      (E'j\u00E4hrlich',
       E'j\u0061\u0308hrlich'))
  AS s(nfc_form, nfd_form);


 nfc_form | nfd_form | equal1 | equal2
----------+----------+--------+--------
 jährlich  | jährlich  | t    | f
(1 row)

Normalizing is available as a built-in function since Postgres 13 and
non-deterministic collations appeared in Postgres 12.


Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: https://www.manitou-mail.org
Twitter: @DanielVerite