ILIKE en PostgreSQL: busqueda sin distincion de mayusculas

ILIKE es el operador de PostgreSQL para buscar por patron sin distinguir mayusculas. Se comporta como LIKE, pero '%Ivan%', '%IVAN%' y '%ivan%' devuelven las mismas filas, justo lo que necesitas al buscar nombres, correos y cualquier texto escrito por el usuario.

Sintaxis basica y comodines

ILIKE usa los mismos dos caracteres especiales que LIKE:

• % coincide con cualquier secuencia de caracteres, incluida la cadena vacia;
• _ coincide con exactamente un caracter.

-- Find users whose name contains "ivan" in any case
SELECT id, name, email
FROM users
WHERE name ILIKE '%ivan%';

-- Email ending in a specific domain, case-insensitive
SELECT id, email
FROM users
WHERE email ILIKE '%@gmail.com';

-- Exactly five characters, starting with "a" or "A"
SELECT name
FROM users
WHERE name ILIKE 'a____';

La negacion se escribe NOT ILIKE. Para buscar un % o _ literal, escapalo con la clausula ESCAPE:

-- Statuses literally containing an underscore, e.g. "in_progress"
SELECT id, status
FROM orders
WHERE status ILIKE '%\_%' ESCAPE '\';

ILIKE frente a lower(col) LIKE lower(...)

Antes de que ILIKE se volviera costumbre, se lograba el mismo efecto con lower():

-- Old-school approach: works, but verbose
SELECT id, name
FROM users
WHERE lower(name) LIKE lower('%Ivan%');

Ambas formas devuelven las mismas filas para texto ASCII, pero hay diferencias:

• ILIKE es mas corto y se lee como la intencion de "busqueda sin mayusculas";
• lower() envuelve cada valor en una llamada de funcion, asi que un indice B-tree normal sobre name no se usa salvo que crees un indice funcional sobre lower(name);
• ILIKE tambien normaliza el caso internamente y tampoco puede usar un B-tree normal con un patron que empieza por %.

Gotcha: ni ILIKE ni lower(...) LIKE aplican todas las reglas de mayusculas Unicode igual en todas las locales. La I/i turca y pares parecidos pueden comportarse de forma rara. Para normalizar con rigor, valora el tipo citext o un COLLATE explicito.

Rendimiento e indices pg_trgm

El problema central: un patron como '%ivan%' con % inicial no puede usar un indice normal, asi que PostgreSQL recurre a un escaneo secuencial. La solucion es la extension pg_trgm y un indice GIN sobre trigramas.

-- Enable the extension once per database
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- GIN index that speeds up ILIKE and LIKE with leading wildcards
CREATE INDEX idx_users_name_trgm
ON users
USING gin (name gin_trgm_ops);

-- Now this can use the index instead of a full scan
SELECT id, name
FROM users
WHERE name ILIKE '%ivan%';

gin_trgm_ops soporta tanto LIKE como ILIKE. Comprueba siempre con EXPLAIN ANALYZE: en tablas grandes la diferencia entre un Seq Scan y un Bitmap Index Scan es enorme. Otra opcion es citext mas un indice normal cuando necesitas igualdad o busqueda por prefijo en vez de una subcadena en medio.

MySQL y ClickHouse

MySQL no tiene un ILIKE aparte, y normalmente no hace falta: con una colacion por defecto como utf8mb4_0900_ai_ci, el sufijo _ci significa case-insensitive, asi que LIKE ya ignora las mayusculas.

-- MySQL: LIKE is case-insensitive under a _ci collation
SELECT id, name
FROM users
WHERE name LIKE '%ivan%';

-- Force case-sensitive matching when you actually need it
SELECT id, name
FROM users
WHERE name LIKE '%ivan%' COLLATE utf8mb4_bin;

ClickHouse si tiene ILIKE y funciona como en PostgreSQL; para subcadenas tambien resultan utiles positionCaseInsensitive() y multiSearchAnyCaseInsensitive().

En resumen: en PostgreSQL usa ILIKE por legibilidad y agrega un indice GIN de pg_trgm para la velocidad; en MySQL un LIKE normal bajo una colacion _ci ya es insensible a mayusculas.