ILIKE in PostgreSQL: Case-Insensitive Pattern Matching

Когда пользователь ищет что-то на сайте, он редко думает о регистре букв.

Он может написать:

ivan

Может написать:

Ivan

А может вообще так:

IVAN

Для человека это один и тот же запрос. Но для базы данных строки в разном регистре могут быть разными значениями.

Например:

'Ivan'
'ivan'
'IVAN'
'iVaN'

Если использовать обычный LIKE, PostgreSQL будет учитывать регистр. То есть поиск по ivan может не найти строку Ivan.

Чтобы искать текст без учёта регистра, в PostgreSQL есть оператор ILIKE.

Он работает почти как LIKE, но игнорирует разницу между большими и маленькими буквами.

Чем ILIKE отличается от LIKE

Начнём с простого примера.

Допустим, в таблице users есть пользователи:

id | name
---+--------
1  | Ivan
2  | ivan
3  | IVAN
4  | Petr

Если написать запрос через обычный LIKE:

SELECT id, name
FROM users
WHERE name LIKE '%ivan%';

PostgreSQL будет искать именно строку ivan в нижнем регистре.

В результат может попасть только:

id | name
---+------
2  | ivan

А вот строки Ivan и IVAN могут не попасть, потому что регистр отличается.

Теперь используем ILIKE:

SELECT id, name
FROM users
WHERE name ILIKE '%ivan%';

Результат:

id | name
---+------
1  | Ivan
2  | ivan
3  | IVAN

ILIKE говорит базе:

Найди строки, где есть ivan, но не обращай внимания на регистр букв.

Именно поэтому ILIKE так часто используют для поиска по пользовательскому вводу: имени, email, названию компании, статусу, городу, стране или текстовому описанию.

Базовый синтаксис ILIKE

Синтаксис простой:

column ILIKE pattern

Например:

SELECT id, name, email
FROM users
WHERE name ILIKE '%ivan%';

Здесь:

• name — колонка, по которой ищем;
• ILIKE — поиск без учёта регистра;
• '%ivan%' — шаблон поиска.

Шаблон работает так же, как в LIKE.

Есть два важных специальных символа:

%  — любая последовательность символов
_  — ровно один любой символ

Разберём их отдельно.

Символ %: любое количество символов

Символ % означает: на этом месте может быть что угодно.

Например:

WHERE name ILIKE 'ivan%'

Такой шаблон означает:

строка должна начинаться с ivan, а дальше может быть что угодно.

Он найдёт:

Ivan
Ivanov
ivanka
IVAN Petrov

Но не найдёт:

Sergey Ivanov

Потому что там Ivan находится не в начале строки.

Если написать так:

WHERE name ILIKE '%ivan'

это означает:

строка должна заканчиваться на ivan.

Такой шаблон может найти:

ivan
sergey ivan

Но не найдёт:

Ivanov

А самый популярный вариант — проценты с двух сторон:

WHERE name ILIKE '%ivan%'

Он означает:

найди ivan в любом месте строки.

Такой поиск найдёт:

Ivan
Ivanov
Sergey Ivanov
test_ivan_01

Для обычного поиска по сайту чаще всего используют именно такой вариант.

Символ _: ровно один любой символ

Символ _ означает один любой символ.

Например:

SELECT name
FROM users
WHERE name ILIKE 'a____';

Этот шаблон означает:

строка должна начинаться с a или A, а после неё должно быть ещё ровно 4 символа.

То есть всего в имени должно быть 5 символов.

Такой запрос может найти:

Alice
admin

Но не найдёт:

Ann
Alexander

Потому что Ann слишком короткое, а Alexander слишком длинное.

На практике _ используют реже, чем %, но его полезно знать. Он удобен, когда у строки есть фиксированный формат: код, маска, короткий идентификатор.

Пример: поиск пользователя по имени

Допустим, есть таблица users:

id | name           | email
---+----------------+----------------------
1  | Ivan Petrov    | ivan@mail.com
2  | ivan Sidorov   | test@gmail.com
3  | IVANOV Sergey  | sergey@example.com
4  | Anna Smirnova  | anna@mail.com

Хотим найти всех пользователей, у которых в имени встречается ivan, независимо от регистра.

SELECT
  id,
  name,
  email
FROM users
WHERE name ILIKE '%ivan%';

Результат:

id | name          | email
---+---------------+-------------------
1  | Ivan Petrov   | ivan@mail.com
2  | ivan Sidorov  | test@gmail.com
3  | IVANOV Sergey | sergey@example.com

Это классический сценарий для ILIKE.

Пользователь в поиске ввёл ivan, а мы нашли все подходящие варианты: Ivan, ivan, IVANOV.

Пример: поиск по домену email

Ещё один частый пример — поиск пользователей с определённым почтовым доменом.

Допустим, в таблице есть такие email:

id | email
---+----------------------
1  | anna@gmail.com
2  | bob@GMAIL.COM
3  | kate@Gmail.com
4  | tom@mail.ru

Если мы хотим найти всех пользователей с Gmail, лучше использовать ILIKE:

SELECT
  id,
  email
FROM users
WHERE email ILIKE '%@gmail.com';

Результат:

id | email
---+----------------
1  | anna@gmail.com
2  | bob@GMAIL.COM
3  | kate@Gmail.com

Почему шаблон начинается с %?

Потому что до @gmail.com может быть любая часть email:

anna
bob
kate

А вот конец строки должен совпадать с @gmail.com.

Пример: поиск статусов без учёта регистра

В реальных проектах статусы из разных систем могут приходить в разном регистре.

Например, в таблице orders:

id | status
---+------------
1  | paid
2  | Paid
3  | PAID
4  | failed
5  | pending

Если написать:

SELECT id, status
FROM orders
WHERE status = 'paid';

мы найдём только точное значение paid.

А если нужно найти все варианты, можно использовать ILIKE:

SELECT id, status
FROM orders
WHERE status ILIKE 'paid';

Результат:

id | status
---+--------
1  | paid
2  | Paid
3  | PAID

Здесь мы не используем %, потому что хотим найти именно статус paid, а не любую строку, где paid просто встречается внутри.

То есть:

status ILIKE 'paid'

означает точное совпадение без учёта регистра.

А вот так:

status ILIKE '%paid%'

означает, что paid может быть где угодно внутри строки.

Например, такой шаблон может найти:

paid
not_paid
paid_by_card
prepaid

Поэтому важно выбирать шаблон осознанно.

NOT ILIKE: найти всё, что не подходит

У ILIKE есть отрицательная форма — NOT ILIKE.

Например, хотим найти пользователей, у которых email не Gmail:

SELECT
  id,
  email
FROM users
WHERE email NOT ILIKE '%@gmail.com';

Такой запрос вернёт все email, которые не заканчиваются на @gmail.com, независимо от регистра.

Ещё пример: найти заказы, где статус не содержит paid:

SELECT
  id,
  status
FROM orders
WHERE status NOT ILIKE '%paid%';

Но с таким условием нужно быть внимательнее. Если есть статус not_paid, он содержит текст paid, поэтому под условие NOT ILIKE '%paid%' он не попадёт.

Иногда лучше искать точные значения:

WHERE status NOT ILIKE 'paid'

А не подстроку внутри статуса.

Когда нужны проценты, а когда нет

Это частая ошибка новичков.

Посмотрим на три варианта.

Точное совпадение без учёта регистра

WHERE status ILIKE 'paid'

Найдёт:

paid
Paid
PAID

Но не найдёт:

paid_by_card
not_paid

Начинается с текста

WHERE status ILIKE 'paid%'

Найдёт:

paid
paid_by_card
Paid online

Но не найдёт:

not_paid

Содержит текст где угодно

WHERE status ILIKE '%paid%'

Найдёт:

paid
paid_by_card
not_paid
prepaid

Этот вариант самый широкий. Он удобен для поиска, но иногда может находить лишнее.

Можно запомнить так:

'text'    — ровно text
'text%'   — начинается с text
'%text'   — заканчивается на text
'%text%'  — содержит text где угодно

Как искать настоящий символ % или _

Символы % и _ в ILIKE имеют специальный смысл.

Но иногда нужно найти именно сам символ % или _.

Например, в статусах есть значения:

in_progress
on_hold
payment_failed

И мы хотим найти строки, где есть настоящий символ подчёркивания _.

Если написать так:

WHERE status ILIKE '%_%'

это будет не совсем то, что нужно.

Почему?

Потому что _ означает один любой символ. Такой шаблон будет подходить почти под любую непустую строку.

Чтобы искать именно символ _, его нужно экранировать.

SELECT
  id,
  status
FROM orders
WHERE status ILIKE '%\_%' ESCAPE '\';

Здесь:

ESCAPE '\'

говорит базе:

символ \ используется для экранирования специальных символов.

А шаблон:

'%\_%'

означает:

найди строку, где есть настоящий символ _.

То же самое можно делать с %, если нужно найти именно знак процента.

ILIKE и lower(): два способа решить одну задачу

До ILIKE или вместо него иногда используют такой подход:

SELECT id, name
FROM users
WHERE LOWER(name) LIKE LOWER('%Ivan%');

Что здесь происходит?

1. LOWER(name) переводит имя из таблицы в нижний регистр.
2. LOWER('%Ivan%') переводит шаблон в нижний регистр.
3. Потом обычный LIKE сравнивает уже два значения в нижнем регистре.

То есть идея такая же: сделать поиск независимым от регистра.

Запрос:

WHERE LOWER(name) LIKE LOWER('%Ivan%')

по смыслу похож на:

WHERE name ILIKE '%Ivan%'

Но ILIKE обычно читается проще.

Когда человек видит:

name ILIKE '%ivan%'

он сразу понимает:

это поиск по шаблону без учёта регистра.

А вариант с LOWER длиннее и больше похож на технический обходной путь.

Для обычных PostgreSQL-запросов чаще удобнее использовать ILIKE.

Важный момент про индексы

На маленьких таблицах можно почти не думать о производительности. Но если в таблице миллионы строк, поиск через ILIKE '%text%' может стать тяжёлым.

Почему?

Обычный индекс по колонке хорошо помогает, когда база понимает, с чего начинается значение.

Например, для поиска по точному email:

WHERE email = 'anna@gmail.com'

индекс может быть очень полезен.

Но для такого поиска:

WHERE name ILIKE '%ivan%'

шаблон начинается с %.

Это означает:

перед ivan может быть что угодно.

Строка может быть:

Ivan
Sergey Ivanov
test_ivan_01
user ivan old

Обычному B-tree индексу сложно использовать такой шаблон, потому что поиск идёт не от начала строки.

В итоге PostgreSQL может пойти по всей таблице и проверить каждую строку.

На маленькой таблице это нормально. На большой — может быть медленно.

pg_trgm: как ускорить ILIKE '%text%'

Для ускорения поиска подстроки в PostgreSQL часто используют расширение pg_trgm.

Оно работает на основе триграмм.

Триграмма — это кусочек строки из трёх символов. PostgreSQL может разбить текст на такие кусочки и быстрее искать похожие или содержащие нужную подстроку значения.

Сначала нужно включить расширение в базе:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

Обычно это делают один раз на базу данных.

Потом можно создать GIN-индекс:

CREATE INDEX idx_users_name_trgm
ON users
USING gin (name gin_trgm_ops);

После этого такой запрос может начать работать быстрее:

SELECT
  id,
  name
FROM users
WHERE name ILIKE '%ivan%';

Но важно не просто создать индекс, а проверить, что PostgreSQL действительно его использует.

Для этого применяют EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT
  id,
  name
FROM users
WHERE name ILIKE '%ivan%';

В плане запроса можно увидеть, как база выполняет поиск. Если всё хорошо, вместо полного прохода по таблице может появиться индексный план, например через Bitmap Index Scan.

Для новичка главное запомнить:

ILIKE '%text%' удобен, но на большой таблице может быть медленным. Для ускорения такого поиска в PostgreSQL часто используют pg_trgm и GIN-индекс.

Если нужен не поиск, а точное сравнение без регистра

Иногда ILIKE используют там, где на самом деле нужен не поиск по шаблону, а точное сравнение без учёта регистра.

Например:

WHERE email ILIKE 'anna@gmail.com'

Это сработает. Но если в проекте часто нужны case-insensitive сравнения по email, можно подумать о других решениях.

Например:

• хранить email сразу в нижнем регистре;
• использовать отдельную нормализованную колонку;
• создать функциональный индекс по LOWER(email);
• использовать тип citext в PostgreSQL.

Тип citext похож на обычный текст, но сравнения для него выполняются без учёта регистра.

Например, если колонка email имеет тип citext, то значения:

Anna@Gmail.com
anna@gmail.com
ANNA@GMAIL.COM

могут сравниваться как одинаковые.

Но citext — это уже решение на уровне структуры базы. Его стоит выбирать осознанно, когда вы проектируете таблицу и точно понимаете, что регистр в этой колонке не должен иметь значения.

Для простых запросов и учебных задач достаточно ILIKE.

Unicode и локали: редкая, но важная тонкость

Для обычного поиска по латинице и кириллице ILIKE чаще всего ведёт себя ожидаемо.

Но в некоторых языках и локалях регистр букв устроен сложнее, чем просто «большая буква — маленькая буква».

Классический пример — турецкие буквы I и i, где правила преобразования регистра могут отличаться от привычных английских.

Для большинства учебных и продуктовых задач это не станет проблемой. Но если вы делаете поиск, где языковые правила критичны, лучше заранее подумать о:

• локали базы данных;
• collation;
• типе citext;
• отдельной поисковой системе;
• нормализации текста перед сохранением.

Иначе можно столкнуться с редкими, но неприятными расхождениями в поиске.

ILIKE в WHERE и GROUP BY

ILIKE чаще всего используют в WHERE, чтобы отфильтровать строки.

Например:

SELECT id, name
FROM users
WHERE name ILIKE '%ivan%';

Но иногда его используют внутри условий для аналитики.

Например, посчитать количество заказов с разными группами статусов:

SELECT
  COUNT(*) FILTER (WHERE status ILIKE 'paid') AS paid_orders,
  COUNT(*) FILTER (WHERE status ILIKE 'failed') AS failed_orders,
  COUNT(*) FILTER (WHERE status ILIKE 'pending') AS pending_orders
FROM orders;

Такой запрос полезен, если статусы пришли из разных источников и могут быть в разном регистре:

paid
Paid
PAID

Но если статусы — важные бизнес-значения, лучше хранить их в едином формате, например всегда в нижнем регистре.

Тогда запросы будут проще:

WHERE status = 'paid'

А ILIKE останется для настоящего текстового поиска.

ILIKE не заменяет нормальную очистку данных

ILIKE помогает искать без учёта регистра, но он не исправляет данные.

Если в таблице есть значения:

paid
 Paid
PAID
 paid

можно искать их так:

WHERE status ILIKE 'paid'

Но строки с пробелами по краям могут всё равно создать проблемы.

Например:

' paid '

не равно:

'paid'

Даже если не учитывать регистр, пробелы остаются.

В таком случае может понадобиться TRIM:

WHERE TRIM(status) ILIKE 'paid'

А ещё лучше — очищать данные при загрузке или перед сохранением, чтобы в таблице сразу лежали нормальные значения:

paid
failed
pending

Хороший принцип:

ILIKE нужен для поиска без учёта регистра, а не для исправления грязных данных.

Если данные постоянно приходят в разном формате, лучше нормализовать их заранее.

Аналог в MySQL

В MySQL отдельного оператора ILIKE обычно нет.

Но часто он и не нужен, потому что поведение LIKE зависит от collation — правил сравнения строк.

Во многих популярных collation для MySQL суффикс _ci означает case-insensitive, то есть без учёта регистра.

Например, если колонка использует case-insensitive collation, такой запрос:

SELECT id, name
FROM users
WHERE name LIKE '%ivan%';

может найти:

Ivan
ivan
IVAN

То есть обычный LIKE уже будет вести себя похоже на PostgreSQL ILIKE.

Если же нужен поиск с учётом регистра, можно использовать бинарную collation.

Например:

SELECT id, name
FROM users
WHERE name LIKE '%ivan%' COLLATE utf8mb4_bin;

Такой поиск будет чувствителен к регистру.

Главная мысль:

В PostgreSQL для поиска без учёта регистра обычно используют ILIKE. В MySQL это часто зависит от collation, и обычный LIKE может уже быть case-insensitive.

Аналог в ClickHouse

В ClickHouse оператор ILIKE тоже есть.

Пример:

SELECT id, name
FROM users
WHERE name ILIKE '%ivan%';

Он используется для поиска по шаблону без учёта регистра.

Также в ClickHouse есть функции для поиска подстрок без учёта регистра.

Например:

SELECT positionCaseInsensitive(name, 'ivan')
FROM users;

Эта функция возвращает позицию найденной подстроки без учёта регистра.

Если нужно проверить, что строка содержит подстроку, можно использовать условие:

SELECT id, name
FROM users
WHERE positionCaseInsensitive(name, 'ivan') > 0;

Для более сложных сценариев в ClickHouse есть и другие функции поиска, например multiSearchAnyCaseInsensitive.

Но для простого шаблонного поиска ILIKE читается понятнее.

Практические шаблоны

Найти имя, содержащее текст

SELECT id, name
FROM users
WHERE name ILIKE '%ivan%';

Найти имя, начинающееся с текста

SELECT id, name
FROM users
WHERE name ILIKE 'ivan%';

Найти email на Gmail

SELECT id, email
FROM users
WHERE email ILIKE '%@gmail.com';

Найти точный статус без учёта регистра

SELECT id, status
FROM orders
WHERE status ILIKE 'paid';

Найти статус, содержащий текст

SELECT id, status
FROM orders
WHERE status ILIKE '%paid%';

Исключить Gmail-адреса

SELECT id, email
FROM users
WHERE email NOT ILIKE '%@gmail.com';

Найти настоящий символ подчёркивания

SELECT id, status
FROM orders
WHERE status ILIKE '%\_%' ESCAPE '\';

Ускорить поиск подстроки через pg_trgm

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_users_name_trgm
ON users
USING gin (name gin_trgm_ops);

После этого можно проверять план:

EXPLAIN ANALYZE
SELECT id, name
FROM users
WHERE name ILIKE '%ivan%';

Что важно запомнить

ILIKE — это PostgreSQL-оператор для поиска по шаблону без учёта регистра.

Он похож на LIKE, но не различает большие и маленькие буквы.

Пример:

SELECT id, name
FROM users
WHERE name ILIKE '%ivan%';

Такой запрос найдёт:

Ivan
ivan
IVAN
Ivanov

В шаблонах используются те же символы, что и в LIKE:

%  — любое количество символов
_  — ровно один любой символ

Разница между шаблонами:

'ivan'    — точное совпадение
'ivan%'   — начинается с ivan
'%ivan'   — заканчивается на ivan
'%ivan%'  — содержит ivan где угодно

Для отрицания используется NOT ILIKE.

WHERE email NOT ILIKE '%@gmail.com'

Если нужно найти настоящий символ % или _, используйте экранирование через ESCAPE.

Короткий вывод

ILIKE нужен, когда пользовательский поиск не должен зависеть от регистра.

Например, человек ввёл ivan, а мы хотим найти и Ivan, и IVAN, и ivanov.

SELECT id, name
FROM users
WHERE name ILIKE '%ivan%';

Это простой и читаемый способ сделать поиск удобнее.

Но важно помнить про производительность. Если запрос выглядит так:

WHERE name ILIKE '%ivan%'

и таблица большая, PostgreSQL может читать много строк подряд. Для ускорения такого поиска часто используют расширение pg_trgm и GIN-индекс.

Главная идея простая:

ILIKE делает поиск дружелюбным к пользователю, а правильные индексы делают его дружелюбным к базе данных.