El módulo citext proporciona un tipo de cadena de caracteres
insensible a mayúsculas y minúsculas, citext. Esencialmente, llama
internamente a lower al comparar valores. Por lo demás, se comporta
casi exactamente igual que text.
Considera utilizar intercalaciones no deterministas (ver Section 23.2.2.4) en lugar de este módulo. Se pueden usar para comparaciones insensibles a mayúsculas y minúsculas, comparaciones insensibles a acentos y otras combinaciones, y manejan correctamente más casos especiales de Unicode.
Este módulo se considera “trusted” (de confianza), es decir, puede ser
instalado por usuarios que no sean superusuarios y que tengan el privilegio
CREATE en la base de datos actual.
El enfoque estándar para realizar coincidencias insensibles a mayúsculas y minúsculas
en PostgreSQL ha sido utilizar la función lower
al comparar valores, por ejemplo:
SELECT * FROM tab WHERE lower(col) = LOWER(?);
Esto funciona razonablemente bien, pero tiene una serie de inconvenientes:
Hace que tus sentencias SQL sean verbosas, y siempre tienes que acordarte de
usar lower tanto en la columna como en el valor de la consulta.
No utilizará un índice, a menos que crees un índice funcional utilizando
lower.
Si declaras una columna como UNIQUE o PRIMARY
KEY, el índice generado implícitamente es sensible a mayúsculas y minúsculas. Por lo tanto,
es inútil para búsquedas insensibles a mayúsculas y minúsculas, y no garantizará
la unicidad de forma insensible a mayúsculas y minúsculas.
El tipo de datos citext te permite eliminar las llamadas
a lower en las consultas SQL y permite que una clave primaria
sea insensible a mayúsculas y minúsculas. citext depende de la configuración regional (locale-aware), al igual
que text, lo que significa que la coincidencia de caracteres en mayúsculas y
minúsculas depende de las reglas de la configuración LC_CTYPE de la base
de datos. Nuevamente, este comportamiento es idéntico al uso de lower en las consultas.
Pero debido a que el tipo de datos lo hace de forma transparente, no tienes que acordarte de hacer
nada especial en tus consultas.
Aquí tienes un ejemplo sencillo de uso:
CREATE TABLE users (
nick CITEXT PRIMARY KEY,
pass TEXT NOT NULL
);
INSERT INTO users VALUES ( 'larry', sha256(random()::text::bytea) );
INSERT INTO users VALUES ( 'Tom', sha256(random()::text::bytea) );
INSERT INTO users VALUES ( 'Damian', sha256(random()::text::bytea) );
INSERT INTO users VALUES ( 'NEAL', sha256(random()::text::bytea) );
INSERT INTO users VALUES ( 'Bjørn', sha256(random()::text::bytea) );
SELECT * FROM users WHERE nick = 'Larry';
La sentencia SELECT devolverá una tupla, a pesar de que
la columna nick se estableció en larry y la consulta
buscaba Larry.
citext realiza las comparaciones convirtiendo cada cadena a minúsculas
(como si se llamara a lower) y luego comparando los
resultados normalmente. Así, por ejemplo, dos cadenas se consideran iguales
si lower produciría resultados idénticos para ellas.
Con el fin de emular una intercalación insensible a mayúsculas y minúsculas lo más fielmente posible,
existen versiones específicas de citext para una serie de operadores y funciones
de procesamiento de cadenas. Así, por ejemplo, los operadores de expresiones regulares
~ y ~* presentan el mismo comportamiento cuando se aplican a
citext: ambos coinciden de forma insensible a mayúsculas y minúsculas. Lo mismo se aplica
a !~ y !~*, así como a los operadores LIKE
~~ y ~~*, y !~~ y !~~*.
Si deseas buscar coincidencias con distinción de mayúsculas y minúsculas (sensible), puedes convertir (cast)
los argumentos del operador a text.
Del mismo modo, todas las funciones siguientes realizan coincidencias de forma insensible a
mayúsculas y minúsculas si sus argumentos son de tipo citext:
regexp_match()
regexp_matches()
regexp_replace()
regexp_split_to_array()
regexp_split_to_table()
replace()
split_part()
strpos()
translate()
Para las funciones de expresiones regulares, si deseas buscar coincidencias con distinción de
mayúsculas y minúsculas, puedes especificar la bandera “c” para forzar una coincidencia sensible.
De lo contrario, debes convertir a text antes de usar una de estas funciones si
deseas un comportamiento sensible a mayúsculas y minúsculas.
El comportamiento de conversión de mayúsculas/minúsculas (case-folding) de citext
depende de la configuración de LC_CTYPE de tu base de datos. Por lo tanto, cómo compara
los valores se determina cuando se crea la base de datos. No es verdaderamente
insensible a mayúsculas y minúsculas en los términos definidos por el estándar Unicode.
Efectivamente, lo que esto significa es que, mientras estés conforme con tu
intercalación, deberías estarlo con las comparaciones de citext. Pero
si tienes datos en diferentes idiomas almacenados en tu base de datos, los usuarios
de un idioma pueden encontrar que los resultados de sus consultas no son los esperados si la
intercalación es para otro idioma.
A partir de PostgreSQL 9.1, puedes adjuntar una
especificación COLLATE a las columnas o valores de datos de tipo citext.
Actualmente, los operadores de citext respetarán una especificación COLLATE
no predeterminada al comparar cadenas convertidas, pero la conversión inicial a minúsculas
siempre se realiza de acuerdo con la configuración de LC_CTYPE de la base de datos
(es decir, como si se hubiera proporcionado COLLATE "default"). Esto podría
cambiar en una versión futura para que ambos pasos sigan la especificación COLLATE
de entrada.
citext no es tan eficiente como text porque las
funciones operadoras y las funciones de comparación de B-tree deben hacer copias
de los datos y convertirlos a minúsculas para las comparaciones. Además, solo
text admite la deduplicación de B-Tree. Sin embargo,
citext es ligeramente más eficiente que utilizar la función
lower para obtener coincidencias insensibles a mayúsculas y minúsculas.
citext no ayuda mucho si necesitas comparar los datos con distinción de
mayúsculas y minúsculas en algunos contextos y sin distinción en otros.
La respuesta estándar es utilizar el tipo text y usar manualmente
la función lower cuando necesites comparar sin distinción; esto
funciona bien si la comparación insensible se necesita solo de manera poco frecuente.
Si necesitas un comportamiento insensible la mayor parte del tiempo y sensible rara vez, considera
almacenar los datos como citext y convertir (cast) explícitamente la columna a text
cuando desees una comparación sensible. En cualquier situación, necesitarás
dos índices si deseas que ambos tipos de búsquedas sean rápidos.
El esquema que contiene los operadores de citext debe estar
en el search_path actual (típicamente public);
si no lo está, se invocarán en su lugar los operadores normales de text
sensibles a mayúsculas y minúsculas.
El enfoque de convertir las cadenas a minúsculas para la comparación no maneja correctamente algunos
casos especiales de Unicode, por ejemplo, cuando una letra en mayúscula tiene dos equivalentes
en minúsculas. Unicode distingue entre mapeo de mayúsculas/minúsculas (case mapping)
y conversión de mayúsculas/minúsculas (case folding) por esta razón.
Utiliza intercalaciones no deterministas en lugar de citext para manejar esto correctamente.
David E. Wheeler <[email protected]>
Inspirado en el módulo citext original de Donald Fraser.