PostgreSQL supporte-t-il les collations "insensibles aux accents" ?

Question

Dans Microsoft SQL Server, il est possible de spécifier une collation "insensible aux accents" (pour une base de données, une table ou une colonne), ce qui signifie qu'il est possible pour une requête telle que

SELECT * FROM users WHERE name LIKE 'João'

pour trouver une ligne avec un Joao nom.

Je sais qu'il est possible de supprimer les accents des chaînes de caractères dans PostgreSQL en utilisant la fonction chaîne non accentuée mais je me demande si PostgreSQL prend en charge ces collations "insensibles aux accents", de sorte que la fonction SELECT ci-dessus fonctionnerait.

Answer 1

Utilisez le module non ascendant pour cela - ce qui est complètement différent de ce à quoi vous faites référence.

unaccent est un dictionnaire de recherche de texte qui supprime les accents (diacritique ) des lexèmes.

Installer une fois par base de données avec :

CREATE EXTENSION unaccent;

Si vous obtenez une erreur comme :

ERROR: could not open extension control file
"/usr/share/postgresql/<version>/extension/unaccent.control": No such file or directory

Installez le paquet contrib sur votre serveur de base de données comme indiqué dans cette réponse connexe :

• Erreur lors de la création d'une extension unaccent sur PostgreSQL

Il fournit, entre autres, la fonction unaccent() que vous pouvez utiliser avec votre exemple (où LIKE ne semble pas nécessaire).

SELECT *
FROM   users
WHERE  unaccent(name) = unaccent('João');

Index

Pour utiliser un index pour ce type de requête, créez un fichier indice sur l'expression . Cependant Postgres n'accepte que IMMUTABLE pour les index. Si une fonction peut renvoyer un résultat différent pour la même entrée, l'index pourrait se briser silencieusement.

unaccent() seulement STABLE no IMMUTABLE

Malheureusement, unaccent() est seulement STABLE pas IMMUTABLE . Selon ce fil de discussion sur pgsql-bugs Ceci est dû à trois des raisons :

1. Cela dépend du comportement d'un dictionnaire.
2. Il n'y a pas de connexion câblée à ce dictionnaire.
3. Elle dépend donc également du courant search_path qui peut changer facilement.

Quelques tutoriels sur l'instruction web pour juste modifier la volatilité de la fonction pour IMMUTABLE . Cette méthode de force brute peut se briser dans certaines conditions.

D'autres suggèrent une simple IMMUTABLE fonction wrapper (comme je l'ai fait moi-même dans le passé).

Un débat est en cours pour savoir s'il faut faire de la variante avec deux paramètres IMMUTABLE qui déclare explicitement le dictionnaire utilisé. Lire aquí o aquí .

Une autre alternative serait ce module avec un IMMUTABLE unaccent() fonction par Musicbrainz fourni sur Github. Je ne l'ai pas testé moi-même. Je pense avoir trouvé une meilleure idée :

Le meilleur pour l'instant

Cette approche est plus efficace que les autres solutions qui circulent, et plus sûr .
Créer un IMMUTABLE Fonction wrapper SQL exécutant le formulaire à deux paramètres avec une fonction et un dictionnaire qualifiés par le schéma.

Puisque l'imbrication d'une fonction non immuable désactiverait l'inlining de fonction, il faut la baser sur une copie de la fonction C, (fausse) déclarée IMMUTABLE également. Son site seulement est destiné à être utilisé dans le wrapper de la fonction SQL. Elle n'est pas destinée à être utilisée seule.

La sophistication est nécessaire car il n'y a aucun moyen de câbler le dictionnaire dans la déclaration de la fonction C. (Il faudrait pirater le code C). (Cela nécessiterait de modifier le code C lui-même.) La fonction wrapper SQL permet de le faire et permet à la fois l'inlining de la fonction et indices d'expression.

CREATE OR REPLACE FUNCTION public.immutable_unaccent(regdictionary, text)
  RETURNS text LANGUAGE c IMMUTABLE PARALLEL SAFE STRICT AS
'$libdir/unaccent', 'unaccent_dict';

CREATE OR REPLACE FUNCTION public.f_unaccent(text)
  RETURNS text LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
$func$
SELECT public.immutable_unaccent(regdictionary 'public.unaccent', $1)
$func$;

Drop PARALLEL SAFE des deux fonctions pour Postgres 9.5 ou plus ancien.

public étant le schéma où vous avez installé l'extension ( public est la valeur par défaut).

La déclaration de type explicite ( regdictionary ) se défend contre des attaques hypothétiques avec des variantes surchargées de la fonction par des utilisateurs malveillants.

_{<em>Auparavant, j'ai préconisé une fonction d'encapsulation basée sur la fonction <code>STABLE</code> fonction <code>unaccent()</code> livré avec le module unaccent. Cela a désactivé <a href="https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions" rel="noreferrer">l'alignement des fonctions </a>. Cette version exécute <strong>dix fois plus rapide </strong>que la simple fonction wrapper que j'avais ici plus tôt.<br>Et cela était déjà deux fois plus rapide que la première version qui ajoutait <code>SET search_path = public, pg_temp</code> à la fonction - jusqu'à ce que je découvre que le dictionnaire peut également être qualifié par un schéma. <a href="https://www.postgresql.org/docs/12/unaccent.html#id-1.11.7.52.7" rel="noreferrer">Encore (Postgres 12) pas trop évident dans la documentation.</a></em>}

Si vous ne disposez pas des privilèges nécessaires pour créer des fonctions C, vous en revenez à la deuxième meilleure implémentation : Un site IMMUTABLE autour de la fonction STABLE unaccent() fournie par le module :

CREATE OR REPLACE FUNCTION public.f_unaccent(text)
  RETURNS text AS
$func$
SELECT public.unaccent('public.unaccent', $1)  -- schema-qualify function and dictionary
$func$  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT;

Enfin, le indice d'expression pour faire des recherches rapide :

CREATE INDEX users_unaccent_name_idx ON users(public.f_unaccent(name));

N'oubliez pas de recréer les index impliquant cette fonction après toute modification de la fonction ou du dictionnaire, comme une mise à jour de la version majeure en place qui ne recréerait pas les index. Les versions majeures récentes comportaient toutes des mises à jour de la fonction unaccent module.

Adapter les requêtes pour qu'elles correspondent à l'index (afin que le planificateur de requêtes l'utilise) :

SELECT * FROM users
WHERE  f_unaccent(name) = f_unaccent('João');

Vous n'avez pas besoin de la fonction dans la bonne expression. Là, vous pouvez également fournir des chaînes de caractères non accentuées comme 'Joao' directement.

La rapidité de la fonction ne se traduit pas par des requêtes beaucoup plus rapides à l'aide de la fonction indice d'expression . Cela fonctionne sur des valeurs pré-calculées et est déjà très rapide. Mais la maintenance de l'index et les requêtes n'utilisant pas l'index en bénéficient.

La sécurité des programmes clients a été renforcée avec Postgres 10.3 / 9.6.8 etc. Vous pouvez consulter besoin de pour qualifier la fonction de schéma et le nom du dictionnaire comme démontré lorsqu'il est utilisé dans un index. Voir :

• Le dictionnaire de recherche textuelle "unaccent" n'existe pas" dans le journal de postgres, apparemment pendant l'analyse automatique.

Ligatures

Dans Postgres 9.5 ou plus les ligatures comme 'Œ' ou 'ß' doivent être développées manuellement (si vous en avez besoin), puisque unaccent() substitue toujours un simple lettre :

SELECT unaccent('Œ Æ œ æ ß');

unaccent
----------
E A e a S

Vous allez adorer cette mise à jour à unaccent dans Postgres 9.6 :

Étendre le site contrib/unaccent de la norme unaccent.rules pour gérer tous les diacritiques connus d'Unicode, et développer correctement les ligatures (Thomas Munro, Léonard Benedetti)

C'est moi qui souligne en gras. Maintenant nous avons :

SELECT unaccent('Œ Æ œ æ ß');

unaccent
----------
OE AE oe ae ss

Correspondance de motifs

Pour LIKE o ILIKE avec des modèles arbitraires, combinez-les avec le module pg_trgm dans PostgreSQL 9.1 ou plus. Créez un index d'expression trigramme GIN (généralement préférable) ou GIST. Exemple pour GIN :

CREATE INDEX users_unaccent_name_trgm_idx ON users
USING gin (f_unaccent(name) gin_trgm_ops);

Peut être utilisé pour des requêtes comme :

SELECT * FROM users
WHERE  f_unaccent(name) LIKE ('%' || f_unaccent('João') || '%');

Les index GIN et GIST sont plus coûteux à maintenir que le simple btree :

• Différence entre l'indice GiST et GIN

Il existe des solutions plus simples pour les motifs ancrés à gauche uniquement. En savoir plus sur le filtrage et les performances :

• Correspondance de motifs avec LIKE, SIMILAR TO ou expressions régulières dans PostgreSQL

pg_trgm fournit également des informations utiles opérateurs pour "similarité" ( % ) et "distance" ( <-> ) .

Les index trigrammes supportent également les expressions régulières simples avec ~ et al. et insensible à la casse le filtrage par motif avec ILIKE :

• Accentuation PostgreSQL + recherche insensible à la casse

Answer 2

Non, PostgreSQL ne supporte pas les collations dans ce sens.

PostgreSQL ne supporte pas les collations de ce type (qu'elles soient sensibles aux accents ou non) car aucune comparaison ne peut retourner égal à moins que les choses soient binaires-égales. Ceci parce qu'en interne, cela introduirait beaucoup de complexités pour des choses comme un index de hachage. C'est pourquoi les collations, dans leur sens le plus strict, sont les suivantes n'affectent que la commande et non l'égalité.

Solutions de contournement

Dictionnaire de recherche en texte intégral qui décompose les lexèmes.

Pour FTS, vous pouvez définir votre propre dictionnaire en utilisant unaccent ,

CREATE EXTENSION unaccent;

CREATE TEXT SEARCH CONFIGURATION mydict ( COPY = simple );
ALTER TEXT SEARCH CONFIGURATION mydict
  ALTER MAPPING FOR hword, hword_part, word
  WITH unaccent, simple;

Que vous pouvez ensuite indexer avec un index fonctionnel,

-- Just some sample data...
CREATE TABLE myTable ( myCol )
  AS VALUES ('fóó bar baz'),('qux quz');

-- No index required, but feel free to create one
CREATE INDEX ON myTable
  USING GIST (to_tsvector('mydict', myCol));

Vous pouvez maintenant l'interroger très simplement

SELECT *
FROM myTable
WHERE to_tsvector('mydict', myCol) @@ 'foo & bar'

    mycol    
-------------
 fóó bar baz
(1 row)

Voir aussi

• Création d'une recherche insensible à la casse et aux accents/diacritiques sur un champ

Peu accentué par lui-même.

El unaccent module peut aussi être utilisé seul sans intégration FTS, pour cela consultez Réponse d'Erwin

Answer 3

Je suis presque sûr que PostgreSQL s'appuie sur le système d'exploitation sous-jacent pour la collation. Il fait soutien créer de nouvelles collations y personnalisation des collations . Je ne suis pas sûr de la quantité de travail que cela pourrait représenter pour vous, cependant. (Ça pourrait être beaucoup.)