Четыре уровня поиска по шаблону
PostgreSQL поддерживает целых четыре механизма pattern matching, и они отличаются мощностью и стоимостью:
LIKE— стандарт SQL, два метасимвола%и_. Чувствителен к регистру.ILIKE— Postgres-специфичныйLIKE, нечувствительный к регистру.SIMILAR TO— стандарт SQL/99, regex-lite с|,*,+,?,().- POSIX regex через операторы
~,~*,!~,!~*— полноценные регулярные выражения.
Чем мощнее механизм, тем дороже он работает. На колонке без индекса — разница незаметна, но как только речь о миллионах строк или о trigram-индексе, разница в выборе оператора может означать миллисекунды против минут.
LIKE — два метасимвола
LIKE 'шаблон' — самый простой и портируемый способ. Два спецсимвола:
%— любая последовательность символов (включая пустую)_— ровно один любой символ
% — жадная маска любой длины. _ — ровно один символ. Никакой ленивости, никакого захвата групп.
LIKE: клиенты с email на example.com и full_name, начинающийся с 'А':
LIKE чувствителен к регистру: LIKE 'аня%' не найдёт «Аня». Это часто удивляет. Для регистро-нечувствительного поиска есть отдельный оператор.
ILIKE — LIKE, но без оглядки на регистр
ILIKE — расширение PostgreSQL (не часть стандарта SQL). Работает идентично LIKE, но игнорирует регистр для обеих сторон сравнения.
ILIKE: найти клиентов по имени независимо от регистра:
Внутри ILIKE приводит обе стороны к нижнему регистру через lower(). Это значит, что обычный b-tree индекс на колонке не используется. Если по этому полю часто ищут — нужен либо
LOWER(col)pg_trgm.
Эскейп спецсимволов
Что если в строке должен быть буквальный % или _? Эскейпим через бэкслеш по умолчанию, или через любой выбранный символ через ESCAPE:
Эскейп: найти строки, в которых реально стоит знак процента:
В строковых литералах PostgreSQL обычная \ обрабатывается по-разному в зависимости от standard_conforming_strings (включён по умолчанию с 9.1). Поэтому надёжнее всего использовать явный ESCAPE '!' с любым удобным символом.
SIMILAR TO — regex-lite из стандарта
SIMILAR TO — это попытка стандарта SQL добавить регулярки. Синтаксис похож на POSIX regex, но:
- Якоря не нужны — шаблон должен совпасть со всей строкой.
- Поддерживаются:
|(или),*(ноль и более),+(один и более),?(ноль или один),()(группировка),[](классы символов). %и_работают так же, как в LIKE.
SIMILAR TO: email, состоящий из латинских букв и заканчивающийся на example.com:
На практике SIMILAR TO редко встречается в продакшен-коде: если уж нужны регулярки, обычно идут сразу к POSIX. SIMILAR TO — это компромисс ради соответствия стандарту, и поддерживается не во всех СУБД одинаково.
POSIX regex — полноценные регулярные выражения
PostgreSQL предоставляет четыре оператора:
~— соответствует regex, регистрозависимо~*— соответствует regex, регистронезависимо!~— НЕ соответствует regex!~*— НЕ соответствует regex, регистронезависимо
Шаблон — обычный POSIX regex с якорями ^ и $, классами \d, \w, группами, квантификаторами.
LIKE — самый дешёвый, POSIX regex — самый дорогой. Выбирай минимально достаточный.
POSIX regex: email-адреса, начинающиеся с буквы и содержащие цифру:
POSIX regex дороже всех. Если можно решить задачу через LIKE 'foo%' (prefix-search), это будет на порядки быстрее на больших таблицах, потому что b-tree индекс умеет искать префиксы напрямую. Регулярки же требуют либо trigram-индекс, либо последовательный скан.
Углубление: эскейп в regex
В POSIX regex бэкслеш означает «следующий символ — спецсимвол» (\d, \w, \s) или «литеральный символ» (\., \?). Но строковый литерал PostgreSQL тоже трактует бэкслеш — поэтому нужен двойной слеш:
Двойной эскейп в POSIX regex — частая ловушка:
Спойлер: escaped_dot = TRUE, literal_dot_no_match = FALSE, unescaped_dot_matches_anything = TRUE. Это типичная причина багов — забыли заэскейпить точку или вопрос в шаблоне.
Альтернатива — использовать
E'foo\\.bar' всегда трактуется однозначно.
Сравнение производительности
Грубая оценка стоимости на колонке без специального индекса (10M строк, PostgreSQL 16):
col = 'foo'с обычным b-tree индексом — мс.col LIKE 'foo%'— мс (b-tree поддерживает prefix).col LIKE '%foo%'— секунды (full scan, индекс бесполезен).col ILIKE 'foo%'— секунды (lower()ломает индекс).col ~ '^foo'— секунды (regex, full scan).col ILIKE '%foo%'+ trigram-индекс черезpg_trgm— мс.
Главный практический вывод: префиксный поиск (LIKE 'foo%') использует обычный индекс. Всё остальное либо требует функционального индекса, либо trigram, либо просто сканирует всю таблицу.
Чек-лист
LIKE— стандарт SQL,%и_, регистрозависимый.ILIKE— Postgres-специфичный, нечувствительный к регистру.SIMILAR TO— regex-lite из стандарта, на практике встречается редко.- POSIX-операторы
~,~*,!~,!~*— полные регулярки. - Эскейпь
%,_через\или явныйESCAPE. В regex — двойной слеш или E-строка. - Префиксный
LIKE 'foo%'использует b-tree индекс.LIKE '%foo%',ILIKE, regex — нет; нужен trigram или functional index. - Выбирай минимально достаточный механизм: чем мощнее, тем дороже.