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

24.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. Elle permet de lever la restriction sur les paramètres LC_COLLATE et LC_CTYPE d'une base de données, qui ne pouvaient pas être modifiés après sa création.

24.2.1. Concepts

Conceptuellement, toute expression d'un type de donnée supportant les collations a une collation. (Les types de données intégrés supportant les collations sont text, varchar, et char. Les types de données définis par l'utilisateur peuvent aussi être marqués comme supportant les collations. Bien entendu, un domaine défini sur un type de données supportant les collations les supporte aussi.) Si l'expression est une référence de colonne, la collation de l'expression est déterminée par la collation de la colonne. Si l'expression est une constante, la collation sera celle par défaut du type 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 tris et les autres opérations ayant besoin de connaître la collation vont échouer.

Lorsque la base de données doit réaliser un tri ou classement de caractères, elle utilise la collation de l'expression entrée. Ce cas se présentera, par exemple, avec des clauses ORDER BY, ou des appels à des fonctions ou opérateurs tels que <. La collation à appliquer à une clause ORDER BY est simplement la collation de la clé de tri. La collation à appliquer pour l'appel à une fonction ou à un opérateur est dérivée 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 convertissent 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 dérivée des collations des arguments est utilisée à l'exécution de l'opération. Si le type du résultat de la fonction ou de l'opérateur supporte les collations, alors cette collation est utilisée dès l'analyse en tant que la collation de la fonction ou de l'opérateur, au cas où une expression voisine nécessiterait de la connaître.

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 quand plusieurs collations différentes apparaissent dans une expression. Une dérivation explicite utilise la clause COLLATE ; dans tous les autres cas, la dérivation de collation est 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 :

  1. Si une expression en entrée porte une dérivation de collation explicite, alors toutes les collations explicitement dérivées des autres expressions en entrée doivent être identiques, sinon une erreur est levée. Si une collation explicitement dérivée 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 implicitement dérivée, ou celle par défaut. Si est présente une collation autre que celle par défaut, alors elle est aussi le résultat de la combinaison des collations. Sinon, le résultat correspond à la collation par défaut.

  3. S'il existe, parmi les expressions en entrée, des collations implicites, qui ne sont pas celles par défaut, et qui entrent en conflit, 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 selon les règles de la locale de_DE, car l'expression combine une collation dérivé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 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 savoir la locale à utiliser, on obtiendra une erreur. Elle 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, dans ce cas suivant, structurellement similaire,

SELECT a || b FROM test1;
    

il n'y aura pas d'erreur, car l'opérateur || ne tient pas compte des collations : son résultat sera le même quelle qu'elle soit.

La collation 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, si le résultat est d'un type supportant les collations. 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, elle, en tient compte. Comme précédemment, ce conflit peut être résolu par l'emploi d'une déclaration explicite de la collation :

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

24.2.2. Gestion des collations

Une collation est un objet du catalogue SQL qui associe un nom SQL à une locale fournie par les bibliothèques installées sur le système. Une définition de collation a un fournisseur, qui spécifie 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 utilisées par la plupart des outils du système. Un autre fournisseur est icu, qui utilise la bibliothèque externe ICU. Les locales ICU ne peuvent être utilisées que si le support d'ICU a été configuré lors de la compilation de PostgreSQL.

Un objet de type collation fourni par la libc pointe sur une combinaison de paramètres LC_COLLATE et LC_CTYPE, comme acceptés 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. Il est donc plus facile de regrouper ces deux paramètres dans un même concept, que de créer une infrastructure différente simplement pour pouvoir positionner LC_CTYPE pour chaque expression.) De la même façon, une collation libc est liée à un encodage de jeu de caractère (voir Section 24.3). Le même nom de collation peut exister pour différents encodages.

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

24.2.2.1. Collations standard

Les collations nommées default, C, et POSIX sont disponibles sur toutes les plateformes. Des collations complémentaires peuvent être disponibles, ou non, en fonction du support au niveau du système d'exploitation. La collation default utilise les valeurs de LC_COLLATE et LC_CTYPE définies à la création de la base de données. Les collations C et POSIX spécifient toutes deux le comportement « C traditionnel », 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.

24.2.2.2. Collations prédéfinies

Si le système d'exploitation permet de supporter plusieurs locales dans un même programme (fonction newlocale et fonctions conjointes), ou si le support d'ICU est configuré, alors initdb peuplera le catalogue système pg_collation avec toutes les locales trouvées sur le système d'exploitation lors de l'initialisation de l'instance.

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

24.2.2.2.1. Collations libc

Par exemple, le système d'exploitation peut fournir 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 laquelle LC_COLLATE et LC_CTYPE seront positionnés à de_DE.utf8. Il créera aussi une collation avec un nom amputé de l'étiquette .utf8. Ainsi, vous pourrez utiliser cette collation sous le nom de_DE, plus facile à écrire, et moins dépendant du jeu de caractères. Néanmoins, notez que l'ensemble initial des noms de collation initdb est dépendant de la plateforme.

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 avec la commande locale -a. Dans le cas où l'on a besoin d'une collation libc avec des valeurs LC_COLLATE et LC_CTYPE différentes, ou si de nouvelles locales sont installées sur le système après l'initialisation de la base de données, une nouvelle collation peut être créée par la commande CREATE COLLATION. On peut aussi importer en masse de nouvelles locales du système d'exploitation avec 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. Ainsi, une collation au nom tronqué, comme de_DE, peut être considérée unique au sein d'une base, bien qu'elle ne le soit pas globalement. L'utilisation de collations au nom tronqué est d'ailleurs recommandée : ce sera une chose de moins à changer si vous avez besoin 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.

24.2.2.2.2. Collations ICU

Avec ICU, il n'est pas raisonnable 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 en a réellement de distinctes. initdb utilise les API d'ICU pour extraire un jeu de locales distinctes, afin de peupler le jeu initial de collations. Les collations fournies par ICU sont créées dans l'environnement SQL avec des noms suivant le format d'étiquettes de langues BCP 47, plus 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, au moment de l'écriture de ces lignes, elles sont équivalentes à de-x-icu.)

und-x-icu (pour « undefined »)

Collation « racine » ICU. Utilisez ceci pour avoir un ordre de tri raisonnable et linguistiquement agnostique.

Certains encodages (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 ».

24.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.

24.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 de la clause locale acceptées par 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 n'est pas souvent nécessaire d'en créer de nouvelles. Des raisons possibles sont l'utilisation d'un autre système de nommage (auquel cas, voir aussi Section 24.2.2.3.3), ou une mise à jour du système d'exploitation pour fournir les définitions des nouvelles locales (auquel cas, voir aussi pg_import_system_collations()).

24.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 pour adapter le comportement du tri à 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 des noms et attributs acceptables dépend de la version ICU précise.

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 « étiquette 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, dans l'environnement SQL, vous pouvez nommer les objets de collation comme vous le voulez. Dans cet exemple, nous suivons le style de nommage que les collations prédéfinies utilisent, qui suit lui-même BCP 47, mais ce n'est pas obligatoire pour les collations définies 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, si 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 24.2.2.4. Sinon, les chaînes qui sont considérées comme égales d'après la collation, mais non équivalentes au niveau des octets seront triées en fonction des valeurs de leurs octets.

Note

De par sa conception, ICU acceptera pratiquement n'importe quelle chaîne comme nom de locale, et la fera correspondre à la locale la plus proche qu'il peut fournir, en utilisant la procédure de « 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 avec 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.

24.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 pour 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";
      

24.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 considérer des chaînes comme égales même si elles sont constituées d'octets différents. Les cas d'usage typiques comprennent des comparaisons insensibles à la casse, ou insensibles aux accents, de même que la comparaison de chaînes dans différentes formes normales Unicode. C'est au fournisseur de collation d'implémenter de telles comparaisons insensibles ; le drapeau donnant la collation comme déterministe n'indique que si les chaînes égales doivent être 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. Bien que les collations non déterministes aient 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 qu'un B-tree ne peut pas utiliser la déduplication dans 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 ne devraient être utilisées que 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.