Le module citext
fournit un type chaîne de caratères
insensible à la casse, citext
. En réalité, il appelle en interne
la fonction lower
lorsqu'il compare des valeurs. Dans
les autres cas, il se comporte presque exactement comme le type
text
.
L'approche standard pour effectuer des rapprochements insensibles à
la casse avec PostgreSQL était d'utiliser la fonction
lower
pour comparer des valeurs. Par exemple :
SELECT * FROM tab WHERE lower(col) = LOWER(?);
Ceci fonctionne plutôt bien, mais présente quelques inconvénients :
Cela rend les ordres SQL bavards, et vous devez sans arrêt vous souvenir
d'utiliser la fonction lower
à la fois sur la
colonne et la valeur de la requête.
Cela n'utilise pas les index, à moins que vous ne créiez un index
fonctionnel avec la fonction lower
.
Si vous déclarez une colonne UNIQUE
ou PRIMARY
KEY
, l'index généré implicitement est sensible à la casse. Il
est donc inutile pour des recherches insensibles à la casse, et il ne va
pas garantir l'unicité de manière insensible à la casse.
Le type de données citext
vous permet d'éviter les appels à
lower
dans les requêtes SQL, et peut rendre une clé
primaire insensible à la casse. citext
tient compte de la
locale, comme text
, ce qui signifie que la comparaison entre
caractères majuscules et minuscules dépend des règles de la locale
paramétrée par LC_CTYPE
de la base de données.
Ici également, le comportement est identique à l'utilisation de la
fonction lower
dans les requêtes. Mais comme cela est fait de
manière transparente par le type de données, vous n'avez pas à vous souvenir
de faire quelque chose de particulier dans vos requêtes.
Voici un exemple simple d'utilisation :
CREATE TABLE users ( nick CITEXT PRIMARY KEY, pass TEXT NOT NULL ); INSERT INTO users VALUES ( 'larry', md5(random()::text) ); INSERT INTO users VALUES ( 'Tom', md5(random()::text) ); INSERT INTO users VALUES ( 'Damian', md5(random()::text) ); INSERT INTO users VALUES ( 'NEAL', md5(random()::text) ); INSERT INTO users VALUES ( 'Bjørn', md5(random()::text) ); SELECT * FROM users WHERE nick = 'Larry';
L'ordre SELECT
va renvoyer un enregistrement, bien que
la colonne nick
ait été positionnée à
larry
et que la requête soit pour
Larry
.
citext
réalise des comparaisons en convertissant chaque chaîne
en minuscule (comme si lower
avait été appelé) puis en
comparant normalement les résultats. Du coup, deux chaînes sont considérées
égales si lower
donne un résultat identique pour elles.
Afin d'émuler un tri insensible à la casse de la manière la plus
fidèle possible, il existe des versions spécifiques à citext
de plusieurs opérateurs et fonctions de traitement de chaînes.
Ainsi, par exemple, les opérateurs pour les expressions rationnelles
~
and ~*
ont le même comportement quand
ils sont appliqués au type citext
: ils comparent tous
les deux de manière insensible à la casse.
Cela est aussi vraie pour !~
et !~*
,
et également pour les opérateurs
LIKE
, ~~
,
~~*
, et
!~~
et !~~*
. Si vous voulez faire une
comparaison sensible à la casse, vous pouvez convertir dans un
text
.
De la même façon, toutes les fonctions ci-dessous font une comparaison
insensible à la casse si leurs arguments sont de type
citext
:
regexp_match()
regexp_matches()
regexp_replace()
regexp_split_to_array()
regexp_split_to_table()
replace()
split_part()
strpos()
translate()
Pour les fonctions regexp, si vous voulez effectuer des comparaisons
sensibles à la casse, vous pouvez positionner l'indicateur « c »
pour forcer une comparaison sensible à la casse. Sinon, si vous souhaitez
un comportement sensible à la casse, vous devez convertir dans un type
text
avant d'utiliser une de ces fonctions.
Le comportement de citext
sur la sensibilité à la
casse dépend du paramètre
LC_CTYPE
de votre base de données. Par conséquent,
la manière dont il compare les valeurs est fixée lorsque la base
de données est créée.
Il n'est pas réellement insensible à la casse dans les termes définis par
le standard Unicode.
En pratique, ce que cela signifie est que, tant que vous êtes satisfait
de votre tri, vous devriez être satisfait des comparaisons de
citext
. Mais si vous avez des données stockées dans
différentes langues dans votre base, des utilisateurs de certains langages
pourraient trouver que les résultats de leurs requêtes sont inattendus
si le tri est déterminé pour un autre langage.
À partir de PostgreSQL, vous pouvez attacher
une clause COLLATE
aux colonnes citext
ou à une valeur. Actuellement, les opérateurs citext
honoreront
une valeur COLLATE
différente de la valeur par défaut lors
de la comparaison de chaînes. Par contre, la mise en minuscule se fait
toujours à partir de la configuration LC_CTYPE
de la
base de données (c'est-à-dire comme si COLLATE "default"
avait été donné). Cela pourrait changer dans une prochaine version pour que
les deux étapes suivent la clause COLLATE
specification.
citext
n'est pas aussi performant que text
parce que
les fonctions opérateurs et les fonctions de comparaison B-tree
doivent faire des copies des données et les convertir en minuscules pour les
comparaisons. C'est cependant légèrement plus efficace qu'utiliser
lower
pour obtenir des comparaisons insensibles à la casse.
citext
n'aide pas réellement dans un certain conctexte.
Vos données doivent être comparées de manière sensible à la casse dans
certains contextes, et de manière sensible à la casse dans d'autres
contextes. La réponse habituelle à cette question est d'utiliser le type
text
et d'utiliser manuellement la fonction
lower
lorsque vous avez besoin d'une comparaison
insensible à la casse ; ceci fonctionne très bien si vous avez besoin
peu fréquemment de comparaisons insensibles à la casse. Si vous avez
besoin de comparaisons insensibles à la casse la plupart du temps, pensez
à stocker les données en citext
et à convertir explicitement
les colonnes en text
quand vous voulez une comparaison
sensible à la casse. Dans les deux situations, vous aurez besoin de deux
index si vous voulez que les deux types de recherche soient rapides.
Le schéma contenant les opérateurs citext
doit être dans
le search_path
(généralement
public
) ; dans le cas contraire, les
opérateurs text
sensibles à la casse seront
appelés.
David E. Wheeler <david@kineticode.com>
Inspiré par le module original citext
par Donald Fraser.