ILIKE in PostgreSQL: Case-Insensitive Pattern Matching

ILIKE is the PostgreSQL operator for case-insensitive pattern matching. It behaves like LIKE, but '%Ivan%', '%IVAN%', and '%ivan%' all return the same rows, which is exactly what you want when searching over names, emails, and other user-entered strings.

Basic syntax and wildcards

ILIKE uses the same two special characters as LIKE:

• % matches any sequence of characters, including the empty string;
• _ matches exactly one character.

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

Negate with NOT ILIKE. To match a literal % or _, escape it with an ESCAPE clause:

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

ILIKE vs lower(col) LIKE lower(...)

Before ILIKE became a habit, people reached for lower() to get the same effect:

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

Both forms return the same rows for ASCII text, but they differ:

• ILIKE is shorter and reads as the intent "case-insensitive search";
• lower() wraps every row value in a function call, so a plain B-tree index on name cannot be used unless you build a functional index on lower(name);
• ILIKE also folds case internally and likewise cannot use a plain B-tree for a leading-% pattern.

Gotcha: neither ILIKE nor lower(...) LIKE handles every Unicode case-folding rule identically across locales. Turkish I/i and similar pairs can behave unexpectedly. For strict normalization, consider the citext type or an explicit COLLATE.

Performance and pg_trgm indexes

The core problem: a pattern like '%ivan%' with a leading % cannot use an ordinary index, so PostgreSQL falls back to a sequential scan. The fix is the pg_trgm extension and a GIN index over trigrams.

-- 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 supports both LIKE and ILIKE. Always confirm with EXPLAIN ANALYZE — on large tables the gap between a Seq Scan and a Bitmap Index Scan is dramatic. An alternative is citext plus a regular index when you need equality or prefix search rather than a substring in the middle.

MySQL and ClickHouse

MySQL has no separate ILIKE, and usually does not need one: under a default collation such as utf8mb4_0900_ai_ci, the _ci suffix means case-insensitive, so LIKE already ignores case.

-- 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 does have ILIKE and it works as in PostgreSQL; for substring checks the positionCaseInsensitive() and multiSearchAnyCaseInsensitive() functions are also handy.

Bottom line: in PostgreSQL reach for ILIKE for readability, and add a pg_trgm GIN index for speed; in MySQL a plain LIKE under a _ci collation is already case-insensitive.