ILIKE no PostgreSQL: busca sem diferenciar maiusculas

ILIKE e o operador do PostgreSQL para busca por padrao sem diferenciar maiusculas e minusculas. Ele se comporta como o LIKE, mas '%Ivan%', '%IVAN%' e '%ivan%' retornam as mesmas linhas, exatamente o que voce quer ao pesquisar nomes, emails e qualquer texto digitado pelo usuario.

Sintaxe basica e curingas

ILIKE usa os mesmos dois caracteres especiais do LIKE:

• % corresponde a qualquer sequencia de caracteres, inclusive a vazia;
• _ corresponde a exatamente um caractere.

-- 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____';

A negacao e NOT ILIKE. Para casar um % ou _ literal, use a clausula ESCAPE:

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

ILIKE versus lower(col) LIKE lower(...)

Antes de o ILIKE virar habito, o mesmo efeito vinha de lower():

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

As duas formas retornam as mesmas linhas para texto ASCII, mas ha diferencas:

• ILIKE e mais curto e se le como a intencao de "busca sem maiusculas";
• lower() envolve cada valor numa chamada de funcao, entao um indice B-tree comum em name nao e usado a menos que voce crie um indice funcional sobre lower(name);
• ILIKE tambem normaliza o caso internamente e igualmente nao usa um B-tree comum quando o padrao comeca com %.

Pegadinha: nem ILIKE nem lower(...) LIKE aplicam todas as regras de caixa do Unicode de forma identica em todos os locales. O par I/i do turco e semelhantes podem se comportar de modo inesperado. Para normalizacao rigorosa, considere o tipo citext ou um COLLATE explicito.

Desempenho e indices pg_trgm

O problema central: um padrao como '%ivan%' com % no inicio nao consegue usar um indice comum, entao o PostgreSQL recorre a uma varredura sequencial. A solucao e a extensao pg_trgm e um 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 suporta tanto LIKE quanto ILIKE. Confirme sempre com EXPLAIN ANALYZE: em tabelas grandes a diferenca entre um Seq Scan e um Bitmap Index Scan e enorme. Uma alternativa e citext mais um indice comum quando voce precisa de igualdade ou busca por prefixo, e nao de uma substring no meio.

MySQL e ClickHouse

O MySQL nao tem um ILIKE separado, e em geral nao precisa: sob uma colacao padrao como utf8mb4_0900_ai_ci, o sufixo _ci significa case-insensitive, entao o LIKE ja ignora a caixa.

-- 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;

O ClickHouse tem ILIKE e ele funciona como no PostgreSQL; para substrings, as funcoes positionCaseInsensitive() e multiSearchAnyCaseInsensitive() tambem ajudam.

Resumo: no PostgreSQL prefira ILIKE pela legibilidade e adicione um indice GIN de pg_trgm pela velocidade; no MySQL um LIKE comum sob uma colacao _ci ja e insensivel a maiusculas.