Depuis la version 12, les collations de PostgreSQL peuvent être créées avec un paramètre nommé deterministic, qui peut être vrai ou faux, si bien que les collations sont maintenant soit déterministes (ce qu’elles sont par défaut), soit non déterministes.

Qu’est-ce que ce terme signifie exactement? Il se réfère à ce qu’Unicode appelle comparaisons déterministes ou en VO deterministic comparisons entre chaînes de caractères:

This is a comparison where strings that do not have identical binary contents (optionally, after some process of normalization) will compare as unequal

En français, c’est une comparaison où les chaînes qui n’ont pas un contenu binaire identique (optionnellement, après un processus de normalisation) ne sont pas égales.

Avant la version 12, les comparaisons de types concernés par les collations dans Postgres sont toujours déterministes d’après la définition ci-dessus. Spécifiquement, lorsque le fournisseur de collation (libc ou ICU) indique que deux chaînes sont égales, un tie-breaker binaire est appliqué, de sorte que seules les chaînes égales au niveau binaire sont considérées comme vraiment égales par le SGBD.

Depuis la version 12, le nouveau paramètre “deterministic” peut être mis à faux dans CREATE COLLATION pour demander que les comparaisons évitent le tie-breaker, de sorte qu’une différence de représentation en mémoire entre les chaînes ne soit pas un obstacle à les reconnaître comme égales si la locale sous-jacente dit qu’elles le sont.

Non seulement cela affecte les comparaisons directes ou les recherches en tables avec une clause WHERE, mais aussi les résultats de GROUP BY, ORDER BY, DISTINCT, PARTITION BY, les constraintes uniques, et tout ce qui implique l’opérateur d’égalité entre chaînes.

Qu’est-ce qui peut être fait avec les collations non déterministes?

La fonctionnalité la plus évidente est la comparaison en ignorant la casse ou les accents, qui peut maintenant être mise en oeuvre avec une clause COLLATE, plutôt qu’en appelant explicitement les fonctions pour translater la casse (upper, lower) et supprimer les accents (unaccent). Maintenant que ceci est accessible via le service de collation, la recommandation classique d’utiliser le type citext datatype pour ces usages peut être reconsidérée.

Au-delà de cet usage, les collations non déterministes permettent de reconnaître l’égalité de chaînes qui sont équivalentes canoniquement (différant uniquement par leur forme normale Unicode), ou qui diffèrent seulement par des séquences compatibles entre elles, ou par la ponctuation, ou par des caractères non affichables.

Excepté pour l’équivalence canonique, ces fonctionnalités de comparaison sont optionnelles, et elles sont activables en déclarant des attributs dans le paramètre locale, et en particulier les niveaux de comparaison.

La doc Unicode Technical Report #35 fournit une table de paramètres de collation avec des clés et valeurs au format BCP47, mais les exemples de ce billet utiliseront la syntaxe “ancienne” des attributs avec ICU: colStrength, colCaseLevel, colAlternate plutôt que les “nouvelles” clés en question (respectivement ks, kc, ka). La raison est que les attributs pré-cités fonctionnent avec toutes les versions de ICU, alors que les clés ne sont reconnues que lorsque PostgreSQL est compilé avec ICU version 54 (sorti en 2014) ou plus récent. Il se trouve que que les binaries pré-compilés pour Windows sont compilés avec ICU version 53, donc au moins pour cette raison il est préférable d’utiliser la syntaxe la plus ancienne.

A présent, voyons une liste des fonctionnalités avancées de comparaison qui sont rendues possibles par les collations non déterministes.

1. Égalité entre séquences équivalentes de points de codes

Il s’agit d’une exigence d’Unicode que PostgreSQL ne pouvait pas satisfaire jusqu’à présent.

Pour comprendre cela, il faut se rappeler que le point de code (codepoint en anglais) est un numéro Unicode, qui désigne le plus souvent un caractère, mais qui par extension peut désigner aussi certains marqueurs dans le flux de texte, comme l’indicateur de direction pour dire que le texte doit s’afficher de gauche à droite ou de droite à gauche, ou encore un accent tout seul qui doit être interprété en lien avec un caractère précédent. Pour le meilleur ou pour le pire, Unicode est nettement plus compliqué qu’une association un-à-un de caractères avec des numéros.

Comme expliqué dans Équivalence Unicode:

L’équivalence canonique est une forme d’équivalence qui préserve visuellement et fonctionnellement les caractères équivalents. Ils ont un codage binaire différents mais représentent un texte identique.

Les collations non déterministes reconnaissent les séquences équivalentes comme égales sans avoir à spécifier d’attribuer particulier dans l’argument locale.

L’exemple ci-dessous qui illustre cela utilise une locale non liée à un langage particulier: déclarée par une chaîne vide, qui sélectionne la collation racine. und peut aussi être utilisée, en tant que tag BCP-47 de 3 lettres signifiant “undefined”. Autrement un code de langue peut être utilisé, suivi optionnellement par un code de “script” (type d’écriture), et un code de région. Par exemple on pourra utiliser 'fr-CA' pour “français tel que pratiqué au Canada”.

Exemple d’équivalent canonique entre les formes normales NFD et NFC:

CREATE COLLATION nd (
   provider = 'icu',
   locale='',   -- or 'und' (pas de langue ou région spécifiée)
   deterministic = false
);

SELECT s1, s2, s1 = s2 COLLATE nd AS equal FROM
  (VALUES (E'El Nin\u0303o', E'El Ni\u00F1o')) AS s(s1,s2);

   s1    |   s2    | equal 
---------+---------+-------
 El Niño | El Niño | t

Par opposition, avec n’importe quelle collation déterministe, on obtiendrait le résultat f pour false dans la colonne equal, étant donné que les chaînes s1 and s2 ne sont pas égales au niveau binaire (c.a.d au niveau des octets qui les composent après encodage).

2. Égalité entre séquences compatibles de points de codes

Sans être équivalentes, des séquences de points de codes peuvent être simplement compatibles, auquel cas elles peuvent optionnellement être considérées comme égales.

Notamment, une ligature typographique peut souvent être représentée avec un seul point de code spécifique ou comme deux caractères distincts qui se suivent.

Au niveau tertiaire (le niveau par défaut), ces séquences ne sont pas égales. Voyons ça en SQL, en réutilisation la collation "nd" définie plus haut:

SELECT s1, s2, s1 = s2 COLLATE nd AS equal FROM
 (VALUES ('shelffull', E'shel\ufb00ull')) AS s(s1,s2);

    s1     |    s2    | equal 
-----------+----------+-------
 shelffull | shelffull | f

Au niveau secondaire en revanche, ces séquences sont considérées comme égales:

CREATE COLLATION nd2 (
  provider = 'icu',
  locale = '@colStrength=secondary', -- ou 'und-u-ks-level2'
  deterministic = false
);

SELECT s1, s2, s1 = s2 COLLATE nd2 AS equal FROM
 (values ('shelffull', E'shel\ufb00ull')) AS s(s1,s2);

    s1     |    s2    | equal 
-----------+----------+-------
 shelffull | shelffull | t

3. Égalité entre chaînes qui diffèrent par la casse

L’usage le plus typique des collations non déterministes est certainement la comparaison insensible à la casse.

Au niveau secondaire, les chaînes qui diffèrent par la casse sont considérées comme égales:

SELECT s1, s2, s1 = s2 COLLATE nd2 AS equal FROM
 (values ('Abc', 'ABC')) AS s(s1,s2);

 s1  | s2  | equal 
-----+-----+-------
 Abc | ABC | t

4. Égalité entre chaînes qui diffèrent par la casse et les accents

Les chaînes qui diffèrent par les accents ou la casse (ou les deux) sont considérées comme égales au niveau primaire:

CREATE COLLATION nd1 (
  provider = 'icu',
  locale = '@colStrength=primary', -- ou 'und-u-ks-level1'
  deterministic = false
);
  
SELECT s1, s2, s1 = s2 COLLATE nd1 AS "equal-nd1",
               s1 = s2 COLLATE nd2 AS "equal-nd2"
 FROM (values ('Été', 'ete')) AS s(s1,s2);

 s1  | s2  | equal-nd1 | equal-nd2 
-----+-----+-----------+-----------
 Été | ete | t         | f

5. Égalité entre chaînes qui diffèrent par les accents mais pas la casse

Il est possible d’ignorer les accents mais pas la casse en restant au niveau primaire de comparaison, et en activant un attribut booléen spécifique: colCaseLevel.

Exemple:

CREATE COLLATION nd2c (
  provider = 'icu',
  locale = 'und@colStrength=primary;colCaseLevel=yes' , -- ou 'und-u-ks-level1-kc'
  deterministic = false
);

SELECT 'Ete' = 'Eté' COLLATE nd2c AS eq1,
       'Ete' = 'ete' COLLATE nd2c AS eq2;
 eq1 | eq2 
-----+-----
 t   | f

6. Ignorer les espaces et la ponctuation

Pour la ponctuation, on peut d’abord l’ignorer complètement, comme indiqué dans le chapitre “Ignore Punctuation” Options de la documentation ICU.

C’est faisable en activant “Alternate Handling” aux niveaux de comparaison de 1 à 3. Etant donné que colStrength=tertiary par défaut, on peut laisser colStrength non spécifié, comme ci-dessous:

CREATE COLLATION "nd3alt" (
  provider = 'icu',
  locale='und@colAlternate=shifted',
  deterministic = false
);

SELECT '{your-name?}' = 'your name' COLLATE "nd3alt" AS equal;
 equal
-------
 t

7. Égalité entre symboles et ponctuation compatibles

colAlternate mis à shifted au niveau de comparaison “quaternary” peut être utilisé pour faire reconnaître l’égalité entre des symboles y compris de ponctuation qui sont équivalent sur le plan linguistique, mais sont exprimés via des codes de points différents. Par exemple HORIZONTAL ELLIPSIS (U+2026) est équivalent à trois points consécutifs en US-ASCII (FULL STOP, U+002E), et FULLWIDTH COMMERCIAL AT (U+FF20) est équivalent à COMMERCIAL AT (U+0040) tel qu’utilisé dans les adresses mail exprimées en caractères US-ASCII.

CREATE COLLATION "nd4alt" (
  provider = 'icu',
  locale='und@colStrength=quaternary;colAlternate=shifted',
  deterministic = false
);

SELECT 'Wow…!' = 'Wow...!' COLLATE "nd4alt" AS equal;
 equal
-------
 t

8. Ignorer les points de codes affectés à des caractères invisibles

Au niveau de comparaison tertiaire ou inférieur, les points de codes qui sont dans les intervalles [\u0001-\u0008], [\u000E-\u001F] [\u007f-\u009F] (caractère de contrôles) sont ignorés dans les comparaisons.

C’est aussi vrai des points de codes pour les caractères d’espacement comme ceux-là (liste non exhaustive ne comprenant que les plus fréquents):

  • SOFT HYPHEN (U+00AD)
  • ZERO WIDTH SPACE (U+200B)
  • INVISIBLE SEPARATOR (U+2063)
  • LEFT-TO-RIGHT MARK (U+200E)
  • RIGHT-TO-LEFT MARK (U+200F)
  • WORD JOINER (U+2060)
  • …et plein d’autres…

Exemple:

SELECT s1, s2, s1 = s2 COLLATE nd AS equal FROM
  (VALUES ('ABC', E'\u200eA\u0001B\u00adC')) AS s(s1,s2);

 s1  |    s2     | equal 
-----+-----------+-------
 ABC | ‎A\x01B­C | t

Pour que ces points de codes ne soient pas ignorés, le niveau de comparaison doit être mis au maximum, c’est-à-dire colStrength=identical (ou ks-identic pour la syntaxe avec tags). A ce niveau, la seule différence avec l’égalité binaire est l’interprétation des séquences en équivalence canonique.

CREATE COLLATION "nd-identic" (
  provider = 'icu',
  locale='und@colStrength=identical', -- or und-u-ks-identic
  deterministic = false
);

SELECT 'abc' = E'a\u0001bc' COLLATE "nd-identic" AS equal;
 equal 
-------
 f

Transformations (au-delà des collations)

Le umlaut allemand est parfois converti en séquences de caractères US-ASCII comme suit:

  • ü => ue, Ü => Ue
  • ö => oe, Ö => Oe
  • ä => ae, Ä => Ae

Ces équivalences ne sont pas reconnues comme égales par les collations ICU, y compris au niveau primaire de comparaison et en spécifiant Allemand (de) comme langue. En revanche ß (eszett) et ss sont égaux au niveau primaire.

A partir de la version 60, ICU fournit de-ASCII comme en tant que règle de transformation. Les transformations sont réalisées par un service différent des collations, et qui n’est pas exposé par PostgreSQL (voir icu_transform() dans icu_ext si vous avez besoin de ce genre de transformation, ou plus généralement des translitérations entre systèmes d’écritures (scripts)).