PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 13.16 » Administration du serveur » Localisation » Support des collations

23.2. Support des collations

Cette fonctionnalité permet de définir par colonne, ou pour chaque requête, la collation utilisée pour déterminer l'ordre de tri et le classement des caractères. Cette fonctionnalité permet de lever la restriction sur les paramètres LC_COLLATE et LC_CTYPE d'une base de données et qui ne pouvaient pas être modifiés après sa création.

23.2.1. Concepts

Conceptuellement, toute expression d'un type de donnée qui est collatable a une collation. (Les types de données intégrés qui supportent une collation sont text, varchar, et char. Les types de données définies par l'utilisateur peuvent aussi être marqués comme supportant la collation, et bien entendu un domaine qui est défini sur un type de données supportant la collation est, lui aussi, collationnable.) Si l'expression est une colonne, la collation de l'expression est déterminée par la collation de la colonne. Si l'expression est une constante, la collation utilisée sera la collation par défaut du type de données de la constante. La collation d'une expression plus complexe est déterminée à partir des différentes collations de ses entrées, comme cela est décrit ci-dessous.

Une expression peut prendre la collation par défaut, « default », c'est à dire la collation définie au niveau de la base de données. Il est possible que la collation d'une expression soit indéterminée. Dans un tel cas, les opérations de tri et les autres opérations qui ont besoin de connaître la collation vont échouer.

Lorsque la base de données doit réaliser un tri ou classement de caractères, alors elle utilisera la collation de l'expression en entrée. Ce cas se présentera, par exemple, si vous employez la clause ORDER BY et des appels à des fonctions ou des opérateurs tels que <. La collation qui s'applique à une clause ORDER BY est simplement la collation de la clé de tri. La collation qui s'applique pour l'appel à une fonction ou à un opérateur est dérivé des arguments, comme décrit plus bas. En plus de s'appliquer aux opérateurs de comparaison, les collations sont également prises en compte par les fonctions qui réalisent les conversions entre minuscules et majuscules, comme lower, upper et initcap ; par les opérateurs de correspondance de motifs et par to_char et les fonctions affiliées.

Pour un appel à une fonction ou un opérateur, la collation est déterminée à partir de la collation des arguments qui sont passés à l'exécution de l'opération. Si une expression voisine nécessite de connaître la collation de la fonction ou de l'opérateur, et si le type de données du résultat de l'appel possède une collation alors cette collation est interprétée comme la collation de l'expression au moment de l'analyse.

Le calcul de la collation d'une expression est réalisé implicitement ou explicitement. Cette distinction affecte la façon dont les collations sont combinées entre elles lorsque plusieurs collations différentes sont utilisées dans une expression. La collation d'une expression peut être déterminée explicitement par l'emploi de la clause COLLATE ; dans les autres cas, la collation est déterminée de manière implicite. Les règles suivantes s'appliquent lorsque plusieurs collations doivent être utilisée en même temps, par exemple dans un appel à une fonction, les règles suivantes s'appliquent:

  1. Si la collation d'une expression d'entrée est déclarée explicitement alors les collations déclarée explicitement pour les autres expressions d'entrées doivent être les mêmes, sinon une erreur est levée. Si une expression en entrée contient une collation explicite, toutes les collations explicitement dérivées parmi les expressions en entrée doivent être identiques. Dans le cas contraire, une erreur est renvoyée. Si une collation dérivée explicitement est présente, elle est le résultat de la combinaison des collations.

  2. Dans les autres cas, toutes les expressions en entrée doivent avoir la même collation, qu'elle soit implicite ou déterminée à partir de la collation par défaut. Si une collation est présente, autre que celle par défaut, elle est le résultat de la combinaison des collations. Sinon, le résultat correspond à la collation par défaut.

  3. S'il existe des collations implicites mais non par défaut qui entrent en conflit avec les expressions en entrée, alors la combinaison ne peut aboutir qu'à une collation indéterminée. Ce n'est pas une erreur sauf si la fonction appelée requiert une application de la collation. Dans ce cas, une erreur est renvoyée lors de l'exécution.

Par exemple, considérez la table définie de la façon suivante:

CREATE TABLE test1 (
    a text COLLATE "de_DE",
    b text COLLATE "es_ES",
    ...
);
    

Ensuite, dans la requête

SELECT a < 'foo' FROM test1;
    

la comparaison < est réalisée en tenant compte des règles de la locale de_DE, parce que l'expression combine la collation calculée implicitement avec la collation par défaut. Mais, dans la requête

	SELECT a < ('foo' COLLATE "fr_FR") FROM test1;
	

la comparaison est effectuée en utilisant les règles de la locale fr_FR, parce que l'utilisation explicite de cette locale prévaut sur la locale déterminée de manière implicite. De plus, avec la requête

	SELECT a < b FROM test1;
	

l'analyseur ne dispose pas des éléments pour déterminer quelle collation employer, car les collations des colonnes a et b sont différentes. Comme l'opérateur < a besoin de connaître quelle locale utiliser, une erreur sera générée. Cette erreur peut être résolue en attachant une déclaration de collation explicite à l'une ou l'autre des expressions d'entrées, soit:

	SELECT a < b COLLATE "de_DE" FROM test1;
    

ou de manière équivalente

	SELECT a COLLATE "de_DE" < b FROM test1;
    

Toutefois, pour un cas structurellement similaire comme

SELECT a || b FROM test1;
    

ne retournera pas d'erreur car l'opérateur || ne tient pas compte des collations: son résultat sera le même quel que soit la collation.

La collation qui est assignée à une fonction ou à une combinaison d'un opérateur avec ses expressions d'entrées s'applique également au résultat de la fonction ou de l'opérateur. Bien évidemment, cela s'applique que si la fonction de l'opérateur délivre un résultat dans un type de données auquel la collation peut s'appliquer. Ainsi, dans la requête

	SELECT * FROM test1 ORDER BY a || 'foo';
	

le tri sera réalisé en fonction des règles de la locale de_DE. Mais cette requête:

SELECT * FROM test1 ORDER BY a || b;
    

retournera une erreur car bien que l'opérateur || ne tienne pas compte des collations de ses expressions, la clause ORDER BY en tient compte. Comme précédemment, ce conflit peut être résolue par l'emploi d'une déclaration explicite de la collation:

SELECT * FROM test1 ORDER BY a || b COLLATE "fr_FR";
    

23.2.2. Gestion des collations

Une collation est un objet du catalogue dont le nom au niveau SQL correspond à une locale fournie par les bibliothèques installées sur le système. Une définition de la collation a un fournisseur spécifiant quelle bibliothèque fournit les données locales. L'un des fournisseurs standards est libc, qui utilise les locales fournies par la bibliothèque C du système. Ce sont les locales les plus utilisées par des outils du système. Un autre fournisseur est icu, qui utilise la bibliothèque externe ICU. Les locales ICU peuvent seulement être utilisées si le support d'ICU a été configuré lors de la construction de PostgreSQL.

Un objet de type collation fourni par libc pointe sur une combinaison de paramètres LC_COLLATE et LC_CTYPE, comme accepté par l'appel système setlocale(). (Comme le nom le suggère, le principal objectif d'une collation est de positionner LC_COLLATE qui contrôle l'ordre de tri. Dans la pratique, il est très rarement nécessaire de définir un paramètre LC_CTYPE différent de LC_COLLATE. De cette façon, il est plus facile de regrouper ces deux paramètres dans un même concept plutôt que de créer une infrastructure différente simplement pour pouvoir positionner LC_CTYPE pour chaque requête.) De la même façon, une collation libc est liée à un jeu de caractère (voir Section 23.3). Ainsi, plusieurs jeux de caractères peuvent utiliser une collation portant le même nom.

Un objet de type collation fourni par icu pointe sur un collateur nommé fourni par la bibliothèque ICU. ICU ne permet pas de paramétrages « collate » et « ctype » séparés, ils sont donc toujours les mêmes. De même, les collations ICU sont indépendantes de l'encodage, donc il n'y a toujours qu'une seule collation ICU pour un nom donné dans une base de données.

23.2.2.1. Standard de collations

Les collations nommées default, C, et POSIX sont disponibles sur toutes les plateformes. Les collations complémentaires seront ou non disponibles en fonction de leur support au niveau du système d'exploitation. La collation default permet d'utiliser les valeurs de LC_COLLATE et LC_CTYPE telles qu'elles ont été définies à la création de la base de données. Les collations C et POSIX spécifie toute deux le comportement « traditionnel C », dans lequel seuls les caractères ASCII de « A » à « Z » sont considérés comme des lettres, et les tris sont ordonnés strictement par valeur de l'octet du code caractère.

En complément, la collation du standard SQL, nommée ucs_basic, est disponible avec l'encodage UTF8. Elle est équivalente à C et trie les données par le point de code Unicode.

23.2.2.2. Collations prédéfinies

Si le système d'exploitation permet à un programme de supporter plusieurs locales (fonction newlocale et fonctions conjointes) ou si le support d'ICU est configuré, alors initdb peuplera le catalogue système pg_collation en se basant sur toutes les locales qu'il trouve sur le système d'exploitation au moment de l'initialisation du cluster de bases de données.

Pour inspecter les locales actuellement disponibles, utilisez la requête SELECT * FROM pg_collation, ou la commande \dOS+ dans psql.

23.2.2.2.1. Collations LibC

Par exemple, le système d'exploitation peut offrir une locale appelée de_DE.utf8. initdb créera alors une collation nommée de_DE.utf8 pour le jeu de caractère UTF8 pour lequel LC_COLLATE et LC_CTYPE sont positionnés à de_DE.utf8. Il créera aussi une collation dont le nom sera amputé du tag .utf8. Ainsi, vous pouvez utiliser cette collation sous le nom de_DE, dont l'écriture est beaucoup plus facile et qui le rend moins dépendant du jeu de caractères. Néanmoins, notez que le nommage de chaque collation collectée par initdb est dépendant de la plateforme utilisée.

Le jeu de collation par défaut fourni par libc pointe directement vers les locales installées sur le système, qui peuvent être listées en utilisant la commande locale -a. Dans le cas où une collation libc avec différentes valeurs pour LC_COLLATE et LC_CTYPE est nécessaire, ou si des nouvelles locales sont installées sur le système après que la base de données soit initialisée, alors une nouvelle collation pourrait être créée en utilisant la commande CREATE COLLATION. De nouvelles locales du système d'exploitation peuvent aussi être importées en masse en utilisant la fonction pg_import_system_collations().

Dans une même base de données, seules les collations qui utilisent le jeu de caractères de la base de données sont prises en compte. Les autres entrées de pg_collation sont ignorées. De cette façon, une collation dont le nom est tronqué, comme de_DE, sera considérée de manière unique au sein d'une même base de données, même si elle ne peut être considérée comme unique à un niveau plus global. L'utilisation de collations dont le nom est tronqué est d'ailleurs recommandée car vous n'aurez pas besoin de la modifier si vous décidez de changer le jeu de caractères de la base de données. Notez toutefois que les collations default, C, et POSIX peuvent être utilisées sans se soucier de l'encodage de la base de données.

PostgreSQL considère les collations comme des objets distincts et incompatibles entre eux, même si elles possèdent des propriétés identiques. Ainsi, par exemple,

SELECT a COLLATE "C" < b COLLATE "POSIX" FROM test1;
      

va afficher une erreur alors que les collations C et POSIX possèdent des propriétés strictement identiques. Il n'est donc pas recommandé de mélanger des collations dont le nom est complet avec des collations dont le nom l'est pas.

23.2.2.2.2. Collations ICU

Avec ICU, il n'est pas nécessaire d'énumérer tous les noms de locales possibles. ICU utilise un système de nommage particulier pour les locales, mais il y a plus de façons de nommer une locale qu'il n'y a actuellement de locales distinctes. initdb utilise l'API ICU pour extraire un jeu de locales distinct afin de peupler le jeu initial de collations. Les collations fournies par ICU sont créées dans l'environnement SQL avec des noms en suivant le format de balises de langues BCP 47, avec une extension d'« utilisation privée » -x-icu ajoutée pour les distinguer des locales de libc.

Voici quelques exemples de collations pouvant être créées :

de-x-icu

Collation allemande, variante par défaut

de-AT-x-icu

Collation allemande pour l'Autriche, variante par défaut

(Il y a aussi, par exemple, de-DE-x-icu ou de-CH-x-icu mais, lorsque cette partie fut rédigée, elles étaient équivalentes à de-x-icu.)

und-x-icu (pour « undefined »)

Collation « root » ICU. Utilisez ceci pour avoir un ordre de tri linguistique agnostique raisonnable.

Certains encodages parmi les moins fréquemment utilisés ne sont pas supportés par ICU. Si c'est le cas pour l'encodage de la base de données, les enregistrements de collations ICU dans pg_collation sont ignorés. Tenter d'en utiliser un renverra une erreur du type « collation "de-x-icu" for encoding "WIN874" does not exist ».

23.2.2.3. Créer de nouveaux objets de collation

Si les collations standards et prédéfinies ne sont pas suffisantes, les utilisateurs peuvent créer leur propres objets de collation en utilisant la commande SQL CREATE COLLATION.

Les collations standards et prédéfinies sont dans le schéma pg_catalog, comme tous les objets prédéfinis. Les collations définies par les utilisateurs doivent être créées dans des schémas utilisateurs. Ceci assure qu'elles seront sauvegardées par pg_dump.

23.2.2.3.1. Collations libc

Les nouvelles collations libc peuvent être créées ainsi :

CREATE COLLATION german (provider = libc, locale = 'de_DE');
      

Les valeurs exactes qui sont acceptables pour la clause locale dans cette commande dépendent du système d'exploitation. Sur les systèmes Unix, la commande locale -a affichera une liste.

Comme les collations libc prédéfinies incluent déjà toutes les collations définies dans le système d'exploitation au moment de l'initialisation de l'instance, il est souvent nécessaire de créer celles qui sont ajoutées après coup. Des raisons possibles seraient l'utilisation d'un autre système de nommage (auquel cas, voir aussi Section 23.2.2.3.3) ou si le système d'exploitation a été mis à jour pour fournir les définitions des nouvelles locales (auquel cas, voir aussi pg_import_system_collations()).

23.2.2.3.2. Collations ICU

ICU permet la personnalisation des collations en dehors de l'ensemble pré- enregistré langue/pays, préchargé par initdb. Les utilisateurs sont encouragés à définir leur propres objets de collation utilisant ces fonctionnalités pour rendre le comportement de tri compatible avec leurs besoins. Voir https://unicode-org.github.io/icu/userguide/locale/ et https://unicode-org.github.io/icu/userguide/collation/api.html pour plus d'informations sur le nommage des locales ICU. L'ensemble de noms et attributs acceptables dépend de la version ICU spécifique.

Voici quelques exemples :

CREATE COLLATION "de-u-co-phonebk-x-icu" (provider = icu, locale = 'de-u-co-phonebk');
CREATE COLLATION "de-u-co-phonebk-x-icu" (provider = icu, locale = 'de@collation=phonebook');

Collationnement allemand avec le type de collationnement d'un carnet d'adresses

Le premier exemple sélectionne la locale ICU en utilisant une « balise de langue » d'après BCP 47. Le deuxième exemple utilise la syntaxe de locale traditionnelle spécifique à ICU. La préférence va au premier style mais il n'est pas supporté par les anciennes versions d'ICU.

Notez que vous pouvez nommer comme vous le voulez les objets de collation dans l'environnement SQL. Dans cet exemple, nous suivons le style de nommage que les collations prédéfinies utilisent, qui suit aussi BCP 47, mais qui n'est pas requis pour les collations définis par l'utilisateur.

CREATE COLLATION "und-u-co-emoji-x-icu" (provider = icu, locale = 'und-u-co-emoji');
CREATE COLLATION "und-u-co-emoji-x-icu" (provider = icu, locale = '@collation=emoji');

Collationnement racine avec un type de collationnement Emoji, d'après l'Unicode Technical Standard #51

Observez comment, dans le système de nommage traditionnel des locales ICU, la locale racine est sélectionnée par une chaîne vide.

CREATE COLLATION latinlast (provider = icu, locale = 'en-u-kr-grek-latn');
CREATE COLLATION latinlast (provider = icu, locale = 'en@colReorder=grek-latn');

Trie les lettres grecques avant les lettres latines. (Par défaut, le latin est avant le grec.)

CREATE COLLATION upperfirst (provider = icu, locale = 'en-u-kf-upper');
CREATE COLLATION upperfirst (provider = icu, locale = 'en@colCaseFirst=upper');

Trie les lettres majuscules avant les lettres minuscules. (La valeur par défaut est les minuscules avant).

CREATE COLLATION special (provider = icu, locale = 'en-u-kf-upper-kr-grek-latn');
CREATE COLLATION special (provider = icu, locale = 'en@colCaseFirst=upper;colReorder=grek-latn');

Combine ces deux options.

CREATE COLLATION numeric (provider = icu, locale = 'en-u-kn-true');
CREATE COLLATION numeric (provider = icu, locale = 'en@colNumeric=yes');

Ordre numérique, trie les séquences de chiffres par leur valeur numérique. Par exemple : A-21 < A-123 (aussi connu sous le nom de tri naturel).

Voir Unicode Technical Standard #35 et BCP 47 pour les détails. La liste des types de collationnement possibles (sous-ensemble co) peut être trouvée dans le dépôt CLDR.

Notez qu'alors que ce système permet la création de collations qui « ignorent la casse » ou « ignorent les accents » ou quelque chose de similaire (en utilisant la clé ks), pour que ces collations se comportent d'une manière réellement insensible à la casse ou aux accents, elles doivent aussi être déclarées comme non déterministes dans CREATE COLLATION; voir Section 23.2.2.4. Le cas échéant, les chaînes qui sont considérées comme égales d'après la collation mais ne sont pas équivalentes au niveau de l'octet seront triées en fonction des valeurs de leurs octets.

Note

Par design, ICU acceptera pratiquement toute chaîne comme nom de locale et la fera correspondre à la locale la plus proche qu'il peut fournir en utilisant la procédure fallback décrite dans sa documentation. De ce fait, il n'y aura pas de retour direct si la spécification d'une collation est composée en utilisant des fonctionnalités que l'installation ICU donnée ne supporte pas. Il est donc recommandé de créer des cas de tests au niveau applicatif pour vérifier que les définitions de collations satisfont les besoins.

23.2.2.3.3. Copier les collations

La commande CREATE COLLATION peut également être utilisée pour créer une nouvelle collation depuis une collation existante, ce qui peut être utile afin d'être capable d'utiliser une collation indépendante du système dans les applications, de créer des noms compatibles, ou d'utiliser une collation fournie par ICU avec un nom plus lisible. Par exemple :

CREATE COLLATION german FROM "de_DE";
CREATE COLLATION french FROM "fr-x-icu";
      

23.2.2.4. Collations non déterministes

Une collation est soit déterministe, soit non déterministe. Une collation déterministe utilise des comparaisons déterministes, ce qui signifie qu'elle considère les chaînes de caractères comme égales seulement si elles sont constituées des mêmes séquences d'octets. Les comparaisons non déterministes peuvent conclure que des chaînes sont égales même si elles sont constituées d'octets différents. Les cas d'usage typiques comprennent la comparaison insensible à la casse, insensible aux accents, de même que la comparaison de chaînes de différentes formes normales Unicode. C'est au fournisseur de collation d'implémenter des comparaisons de ce type ; le drapeau disant que la collation est déterministe indique seulement si les chaînes égales seront départagées ou non par une comparaison au niveau de l'octet. Voir aussi Unicode Technical Standard 10 pour plus d'information sur la terminologie.

Pour créer une collation non déterministe, spécifiez la propriété deterministic = false à CREATE COLLATION, par exemple:

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

Cet exemple utiliserait la collation Unicode standard d'une manière non déterministe. En particulier, elle permettrait à des chaînes exprimées dans différentes formes normales d'être comparées correctement. D'autres exemples plus intéressants utilisent les facultés de personnalisation d'ICU expliquées ci-dessus. Par exemple :

CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
CREATE COLLATION ignore_accents (provider = icu, locale = 'und-u-ks-level1-kc-true', deterministic = false);
     

Toutes les collations standards et prédéfinies sont déterministes, et toutes les collations définies par les utilisateurs sont déterministes par défaut. Alors que les collations non déterministes ont un comportement plus « correct », particulièrement considérant la puissance d'Unicode et ses nombreux cas spécifiques, elles ont aussi quelques inconvénients. D'abord, leur utilisation génère une pénalité de performance. Il est à noter en particulier que le B-tree ne peut pas utiliser la déduplication avec les index utilisant une collation non déterministe. Par ailleurs, certaines opérations ne sont pas possibles avec des collations non déterministes, comme la recherche par motif. Par conséquent, elles devraient être utilisées uniquement dans les cas où elles sont spécifiquement désirables.

Astuce

Pour gérer du texte sous différentes formes de normalisation Unicode, il est possible d'utiliser les fonctions ou expressions normalize et is normalized pour prétraiter ou contrôler les chaînes de caractères, au lieu d'utiliser des collations non déterministes. Il y a des avantages et inconvénients différents à chaque approche.