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 . 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.
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 comparaison. 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 toujours convertir dans un text avant de comparer.
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_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 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 initdb est exécuté pour créer le cluster. 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.
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, une comparaison standard sur text, c'est-à-dire sensible à la casse, sera réalisée.
David E. Wheeler <david@kineticode.com>
Inspiré par le module original citext par Donald Fraser.
chkpass
