Cómo escribir consultas que no distinguen entre mayúsculas y minúsculas en PostgreSQL

Para buscar texto sin exigir que coincidan las mayúsculas y minúsculas, usa ILIKE para búsquedas con patrones, compara valores normalizados con LOWER o UPPER, o utiliza una columna CITEXT si lo habitual es comparar su contenido sin distinguir entre mayúsculas y minúsculas. La opción adecuada depende de si necesitas un patrón, de cómo consultas la columna y de los índices y la configuración regional que se apliquen.

Los ejemplos siguientes usan una tabla animal con las columnas id, age y type, incluida una fila cuyo valor de type es Kitten. El SQL es ilustrativo y no se ha ejecutado en un servidor PostgreSQL.

Usar ILIKE para buscar patrones sin distinguir entre mayúsculas y minúsculas

Para comparar valores sin distinguir entre mayúsculas y minúsculas, escribe type ILIKE 'kitten'. Para buscar un patrón que pueda aparecer dentro de un valor, usa comodines, como en type ILIKE '%kitten%'. % coincide con cualquier secuencia de caracteres, mientras que _ coincide con un solo carácter. ILIKE es la alternativa de PostgreSQL a LIKE para búsquedas que no distinguen entre mayúsculas y minúsculas; su comportamiento depende de la configuración regional activa.

En el uso habitual, LIKE sí distingue entre mayúsculas y minúsculas, por lo que type LIKE 'kitten' no coincide con Kitten si se usa una intercalación que distingue entre ellas. SIMILAR TO emplea una sintaxis de patrones similar a las expresiones regulares de SQL, mientras que ILIKE utiliza la sintaxis más sencilla de comodines de LIKE. Cada uno sirve para necesidades de búsqueda distintas y no son intercambiables en todas las consultas. El operador ~~* de PostgreSQL es otra forma de escribir ILIKE.

Elige un patrón exacto si buscas una coincidencia similar a una igualdad. Añade % o _ solo cuando la búsqueda deba admitir un patrón más amplio.

Normalizar ambos lados de una comparación con LOWER o UPPER

Para comparar valores sin distinguir entre mayúsculas y minúsculas, aplica la misma conversión de mayúsculas y minúsculas a la columna y al valor de búsqueda: LOWER(type) = LOWER('kitten'). También puedes usar UPPER en ambos lados. LOWER es una función que convierte el texto según la configuración regional de la base de datos; INITCAP pone en mayúscula la inicial de cada palabra y no es un sustituto general para comparar sin distinguir entre mayúsculas y minúsculas.

La conversión de mayúsculas y minúsculas y las comparaciones pueden depender de la configuración regional y de la intercalación, especialmente con caracteres propios de cada idioma. No des por hecho que la conversión de texto solo funciona en inglés ni que todas las configuraciones regionales tratan todos los caracteres de la misma manera. Elige y prueba la configuración regional y la intercalación de la base de datos que se ajusten a los datos y a las reglas de comparación.

Añadir índices para búsquedas que no distinguen entre mayúsculas y minúsculas

Un índice normal sobre type quizá no permita atender una condición sobre LOWER(type) como si ambas expresiones fueran equivalentes. Para consultas de igualdad con valores normalizados, crea un índice de expresión que use la misma expresión que la consulta; por ejemplo, CREATE INDEX animal_type_lower_idx ON animal (LOWER(type));. Un índice equivalente sobre UPPER(type) puede servir para una consulta que use UPPER(type).

Un índice solo resulta útil cuando su expresión y su operador son compatibles con la consulta y el planificador decide utilizarlo; no garantiza que la consulta sea más rápida. La extensión pg_trgm de PostgreSQL puede proporcionar índices de trigramas GIN o GiST para búsquedas compatibles con LIKE e ILIKE. Que esto sea útil depende del patrón, de la tabla y de la carga de trabajo. Antes de elegir un índice, compara los planes y los tiempos con datos representativos.

Si necesitas rechazar valores duplicados que solo difieren en mayúsculas y minúsculas, un índice de expresión único como CREATE UNIQUE INDEX animal_type_lower_unique ON animal (LOWER(type)); puede imponer esa regla para los valores cubiertos por la expresión. Es una decisión de esquema: antes de aplicarla, confirma que coincida con las reglas de datos previstas.

Usar CITEXT para columnas que no distinguen entre mayúsculas y minúsculas

La extensión CITEXT proporciona un tipo de texto cuyas comparaciones no distinguen entre mayúsculas y minúsculas, de modo que una igualdad normal puede encontrar valores aunque su escritura no coincida en ese aspecto. Activa la extensión con CREATE EXTENSION citext; y declara la columna como CITEXT, por ejemplo, type CITEXT. Una consulta como SELECT id, age, type FROM animal WHERE type = 'kitten'; puede encontrar entonces un valor almacenado como Kitten, según el comportamiento de comparación sin distinción entre mayúsculas y minúsculas del tipo.

CITEXT resulta práctico cuando comparar sin distinguir entre mayúsculas y minúsculas es el comportamiento habitual de una columna. No es automáticamente más rápido ni menos costoso que otras opciones, y tampoco elimina la necesidad de tener en cuenta los índices, la intercalación y la carga de trabajo. La extensión debe estar disponible y activada en la base de datos. Antes de adoptarla, revisa el comportamiento de comparación documentado para tu configuración regional y tus datos.

Como ilustración portable de la normalización, el siguiente ejemplo de Python compara dos formas de escribir texto ASCII. Solo muestra el comportamiento de las cadenas de Python: no ejecuta SQL de PostgreSQL ni demuestra el comportamiento de las intercalaciones de una base de datos.

stored_type = "Kitten"
search_type = "kitten"
print(stored_type.lower() == search_type.lower())

True