Приветствую всех, читателей! Эта статья предназначена для специалистов уровня junior-middle и будет фокусироваться на практическом применении, объясняя "зачем" и "когда" использовать тот или иной приём, подкрепляя всё это минимальными, но рабочими SQL-примерами.

В момент когда статья уже была почти закончена, я обнаружил схожую статью на хабре. Понимая этот факт, я постарался максимально разнообразить свою статью, дополнив все упущенные моменты и обновив те, что более не актуальны в связи с обновленной версией СУБД. Спасибо за понимание и приятного прочтения!

Регулярные выражения в PostgreSQL: Мощный инструмент для работы со строками

Регулярные выражения (или regex) — это особые текстовые строки, используемые для описания поискового шаблона. В PostgreSQL regex становится незаменимым инструментом, особенно при работе с большими объёмами неструктурированных строковых данных.

Возможно, у кого-то есть вопрос: ***"А для чего нам регулярные выражения в БД?"*** И мы вам ответим:

Регулярные выражения (regex) позволяют описать сложные текстовые шаблоны компактно и гибко. В контексте базы данных они полезны для:

• валидации формата данных (email, телефон, id),

• поиска записей по сложным критериям,

• извлечения структурированных частей из строк (домен из email, код страны из телефона),

• массовых преобразований (реформатирование дат, очищение данных),

• парсинга логов и текстовых полей.

Для решения вышеописанных (и не только) задач, PostgreSQL предлагает три основных подхода к сопоставлению шаблонов:

1.  Традиционный оператор SQL LIKE.

2.  Оператор SIMILAR TO (добавлен в SQL:1999).

3.  Регулярные выражения в стиле POSIX.

Мы рассмотрим каждый из них, уделив особое внимание регулярным выражениям POSIX и функциям, позволяющим выполнять с ними более сложные операции.

1. Базовое сопоставление с помощью операторов LIKE и ILIKE

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

Когда использовать: Для простых поисков, таких как строки, начинающиеся с определённой подстроки, или содержащие подстроку с известным количеством символов.

Основные подстановочные символы LIKE:

• % (знак процента) — соответствует любой последовательности из нуля или более символов.

• _ (знак подчёркивания) — соответствует ровно одному символу.

Чувствительность к регистру:

•  LIKE: Чувствителен к регистру.

• ILIKE: Нечувствителен к регистру (расширение PostgreSQL).

Примеры:

Предположим, у нас есть таблица Student или Email.

-- Найти студентов, чьи имена начинаются с 'John' (чувствительно к регистру)
SELECT * FROM Student WHERE name LIKE 'John%';
-- Вывод:
-- id   |   name   | ...
-- -----+----------+-----
-- 1001 | John Liu | ...

-- Найти имена, начинающиеся с 'P', за которыми следуют ровно четыре символа, затем 'p'
SELECT * FROM Student WHERE name LIKE 'P____p%';
-- Вывод:
-- id   |    name    | ...
-- -----+------------+-----
-- 1007 | Philip Lose | ...
-- 1617 | Philip Mohan | ...

-- Найти студентов, чьи имена содержат 'rob' (нечувствительно к регистру)
SELECT * FROM Student WHERE name ILIKE '%rob%';
-- Вывод:
-- id   |     name      | ...
-- -----+---------------+-----
-- 1619 | Sam Roberts   | ...
-- 2008 | Julia Roberts | ...

Экранирование специальных символов:

Чтобы найти буквальное вхождение % или _, перед ним нужно добавить экранирующий символ (по умолчанию \, но можно изменить с помощью ESCAPE).

-- Найти комментарии, содержащие текст "100%"
SELECT comment_id FROM comments WHERE comment_text LIKE '100\% sure' ESCAPE '\';

Ограничения:

LIKE-поиск всегда рассматривает всю строку. Если шаблон начинается с %, это может замедлить производительность запроса, так как индексы обычно не используются.

2. Оператор SIMILAR TO

Оператор SIMILAR TO занимает промежуточное положение между LIKE и регулярными выражениями POSIX. Он следует стандарту SQL:1999 и объединяет элементы LIKE (подстановочные символы % и _) с синтаксисом, подобным регулярным выражениям.

Когда использовать: Для сопоставлений, которые сложно реализовать с помощью оператора LIKE, но которые не требуют всех возможностей POSIX-регулярок.

Основные метасимволы SIMILAR TO:

• % и _: Работают как в LIKE (любая подстрока / любой один символ).

• |: Обозначает чередование (ИЛИ).

• *: Ноль или более повторений предыдущего элемента.

• +: Одно или более повторений предыдущего элемента.

• ?: Ноль или одно вхождение предыдущего элемента.

• {m}, {m,}, {m,n}: Точное, минимальное или диапазонное количество повторений.

• (): Объединяет элементы в логическую группу.

• []: Определяет класс символов, как в POSIX regex.

Важное отличие: Как и LIKE, SIMILAR TO успешен только в том случае, если шаблон соответствует всей строке.

Примеры:

-- Найти студентов, чьи имена начинаются с 'J', 'R' или 'P' и заканчиваются на 'y'
SELECT * FROM Student WHERE name SIMILAR TO '[JRP]%y';
-- Вывод:
-- id   |     name      | ...
-- -----+---------------+-----
-- 1101 | Jia Grey      | ...
-- 2001 | Reena Roy     | ...
-- 2009 | Pollards Grey | ...

-- Найти студентов, родившихся между 2000 и 2002 годами
SELECT * FROM Student WHERE birth_date::TEXT SIMILAR TO '200-%';
-- Вывод:
-- id   |   name    | ...   | birth_date | ...
-- -----+-----------+-------+------------+-----
-- 1001 | John Liu  | ...   | 2001-04-05 | ...
-- 1003 | Rita Ora  | ...   | 2001-01-14 | ...

3. Расширенное сопоставление с помощью POSIX-регулярных выражений

Регулярные выражения POSIX предоставляют самый мощный механизм для сопоставления шаблонов. В отличие от LIKE и SIMILAR TO, шаблон POSIX regex может совпадать с любой частью строки, если он явно не привязан к началу или концу.

Когда использовать: Для сложных поисков, валидации данных, извлечения и замены, когда простые операторы недостаточны.

Операторы POSIX-регулярных выражений:

• ~: Соответствует регулярному выражению (с учётом регистра).

• ~*: Соответствует регулярному выражению (без учёта регистра).

• !~: Не соответствует регулярному выражению (с учётом регистра).

• !~*: Не соответствует регулярному выражению (без учёта регистра).

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

-- Найти студентов, чьи имена начинаются с 'R' и заканчиваются на 'a' (чувствительно к регистру)
SELECT * FROM Student WHERE name ~ '^R.*a$';
-- Вывод:
-- id   |   name   | ...
-- -----+----------+-----
-- 1003 | Rita Ora | ...

-- Найти студентов, чьи имена содержат подстроку 'Bert' (нечувствительно к регистру)
SELECT * FROM Student WHERE name ~* 'Bert';
-- Вывод:
-- id   |      name      | ...
-- -----+----------------+-----
-- 1110 | Albert Decosta | ...
-- 1619 | Sam Roberts    | ...
-- 2008 | Julia Roberts  | ...

-- Исключить студентов, зачисленных в 2020 году
SELECT * FROM Student WHERE enrollment_date::TEXT !~ '^2020';

4. Функции регулярных выражений

PostgreSQL предоставляет ряд функций для работы с регулярными выражениями, позволяющих подсчитывать вхождения, находить их позиции, извлекать подстроки и выполнять замены.

Важное примечание о версиях: Функции REGEXP_COUNT, REGEXP_INSTR, REGEXP_LIKE и REGEXP_SUBSTR были добавлены в PostgreSQL 15 и выше. Расширенные параметры start и N для REGEXP_REPLACE также доступны с PostgreSQL 15. EDB Postgres Advanced Server v17 также поддерживает REGEXP_COUNT, REGEXP_INSTR, REGEXP_SUBSTR.

4.1. REGEXP_COUNT()

Функция REGEXP_COUNT() подсчитывает количество вхождений шаблона регулярного выражения POSIX в строке.

Синтаксис:

REGEXP_COUNT(srcstr TEXT, pattern TEXT, position DEFAULT 1, modifier DEFAULT NULL)

• srcstr: Строка для поиска.

• pattern: Регулярное выражение для поиска.

• position: Целочисленное значение, указывающее начальную позицию в строке для поиска. По умолчанию 1.

• modifier: Строка флагов, управляющих поведением сопоставления. Например, i для регистронезависимого поиска. Полный список см. в документации PostgreSQL.

Пример:

-- Подсчитать количество вхождений буквы 'i' в строке 'reinitializing' начиная с первой позиции
SELECT REGEXP_COUNT('reinitializing', 'i', 1);
-- Вывод:
-- regexp_count
-- --------------
--      5

-- Подсчитать количество вхождений буквы 'i' начиная с шестой позиции
SELECT REGEXP_COUNT('reinitializing', 'i', 6);
-- Вывод:
-- regexp_count
-- --------------
--      3

4.2. REGEXP_INSTR()

Функция REGEXP_INSTR() возвращает начальную или конечную позицию N-го вхождения шаблона регулярного выражения POSIX в строке, или 0, если совпадений нет.

Синтаксис:

REGEXP_INSTR(srcstr TEXT, pattern TEXT, position INT DEFAULT 1, occurrence INT DEFAULT 1, returnparam INT DEFAULT 0, modifier TEXT DEFAULT NULL, subexpression INT DEFAULT 0)

• srcstr: Строка для поиска.

• pattern: Регулярное выражение.

• position: Начальная позиция в строке для поиска. По умолчанию 1.

• occurrence: Какое по счёту вхождение шаблона вернуть. По умолчанию 1 (первое вхождение).

• returnparam: Указывает, какую позицию вернуть:

  • 0: Позиция первого символа совпадения.

  • >0: Позиция первого символа после* окончания совпадения.

• modifier: Флаги, управляющие поведением (например, i для игнорирования регистра).

• subexpression: Целое число, идентифицирующее подвыражение в скобках, позицию которого нужно вернуть. По умолчанию 0 (позиция всего совпадения).

Пример:

-- Найти позицию первого вхождения трёх последовательных цифр в телефонном номере
SELECT REGEXP_INSTR('800-555-1212', '', 1, 1);
-- Вывод:
-- regexp_instr
-- --------------
--      1

-- Найти позицию второго вхождения трёх последовательных цифр
SELECT REGEXP_INSTR('800-555-1212', '', 1, 2);
-- Вывод:
-- regexp_instr
-- --------------
--      5

4.3. REGEXP_SUBSTR()

Функция REGEXP_SUBSTR() извлекает подстроку, соответствующую шаблону регулярного выражения POSIX, или NULL, если совпадений нет.

Синтаксис:

REGEXP_SUBSTR(srcstr TEXT, pattern TEXT, position INT DEFAULT 1, occurrence INT DEFAULT 1, modifier TEXT DEFAULT NULL, subexpression INT DEFAULT 0)

• srcstr: Строка для поиска.

• pattern: Регулярное выражение.

• position: Начальная позиция для поиска. По умолчанию 1.

• occurrence: Какое по счёту вхождение шаблона вернуть. По умолчанию 1.

• modifier: Флаги, управляющие поведением (например, i для игнорирования регистра).

• subexpression: Целое число, идентифицирующее подвыражение в скобках, значение которого нужно вернуть. По умолчанию 0 (всё совпадение).

Пример:

-- Извлечь первое вхождение трёх последовательных цифр из телефонного номера
SELECT REGEXP_SUBSTR('800-555-1212', '', 1, 1);
-- Вывод:
-- regexp_substr
-- ---------------
--      800

-- Извлечь второе вхождение трёх последовательных цифр
SELECT REGEXP_SUBSTR('800-555-1212', '', 1, 2);
-- Вывод:
-- regexp_substr
-- ---------------
--      555

Параметр subexpression в функции REGEXP_SUBSTR() позволяет извлекать конкретную группу (подвыражение), обозначенную круглыми скобками () в регулярном выражении. Нумерация подвыражений начинается с 1, а значение 0 (по умолчанию) возвращает всё совпадение.

Синтаксис:

REGEXP_SUBSTR(srcstr, pattern, position, occurrence, modifier, subexpression)

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

1. Извлечение домена из email:

SELECT REGEXP_SUBSTR(
     'support@example.com', 
     '([a-zA-Z0-9._%+-]+)@([a-zA-Z0-9.-]+\.[a-zA-Z]{2,})', 
     1, 1, NULL, 2
   ) AS domain;
-- Вывод:
-- domain
-- ---------------
-- example.com

   Здесь:

   - ([a-zA-Z0-9._%+-]+) — группа 1 (логин),

   - ([a-zA-Z0-9.-]+\.[a-zA-Z]{2,}) — группа 2 (домен).

2. Извлечение кода и номера из телефонного номера:

SELECT 
     REGEXP_SUBSTR('800-555-1212', '(\d{3})-(\d{3})-(\d{4})', 1, 1, NULL, 1) AS code1,
     REGEXP_SUBSTR('800-555-1212', '(\d{3})-(\d{3})-(\d{4})', 1, 1, NULL, 2) AS code2,
     REGEXP_SUBSTR('800-555-1212', '(\d{3})-(\d{3})-(\d{4})', 1, 1, NULL, 3) AS number;
-- Вывод:
-- code1 | code2 | number
-------------------------
-- 800   | 555   | 1212

Примечания:

• Если указанное subexpression превышает количество групп в шаблоне, возвращается NULL.

• Используйте modifier для управления поиском (например, 'i' для регистронезависимого поиска).

4.4. REGEXP_LIKE()

Функция REGEXP_LIKE() проверяет, встречается ли совпадение шаблона регулярного выражения POSIX в строке, возвращая логическое значение TRUE или FALSE.

Синтаксис:

REGEXP_LIKE(string TEXT, pattern TEXT, flags TEXT DEFAULT NULL)

• string: Строка для проверки.

• pattern: Регулярное выражение.

• flags: Необязательная текстовая строка с флагами (например, i для регистронезависимого поиска). Без флагов ведёт себя как оператор ~; с флагом i — как ~.

Пример:

-- Проверить, содержит ли строка 'Hello World' слово 'world' (чувствительно к регистру)
SELECT REGEXP_LIKE('Hello World', 'world');
-- Вывод:
-- regexp_like
-- -------------
--     f

-- Проверить, содержит ли строка 'Hello World' слово 'world' (нечувствительно к регистру)
SELECT REGEXP_LIKE('Hello World', 'world', 'i');
-- Вывод:
-- regexp_like
-- -------------
--     t

4.5. REGEXP_MATCHES()

Функция REGEXP_MATCHES() возвращает набор текстовых массивов с совпавшими подстроками из вхождений шаблона регулярного выражения POSIX в строке.

Доступно с PostgreSQL 10 и выше.

Синтаксис:

REGEXP_MATCHES(string TEXT, pattern TEXT, flags TEXT DEFAULT NULL)

• string: Строка для поиска.

• pattern: Регулярное выражение POSIX.

• flags: Флаги, управляющие поведением. Флаг g (global search) позволяет найти все вхождения, а не только первое.

Пример:

-- Извлечь хэштеги из строки
SELECT REGEXP_MATCHES('Learning #PostgreSQL #REGEXP_MATCHES', '#([A-Za-z0-9_]+)', 'g');
-- Вывод:
-- regexp_matches
-- ------------------
-- {PostgreSQL}
-- {REGEXP_MATCHES}

-- Извлечь доменные имена из email-адресов
SELECT REGEXP_MATCHES(Email, '.+@(.*)$') FROM Email;
-- Вывод:
-- regexp_matches
-- -------------------
-- {sqlguide.edu}
-- {pythonguide.com}
-- ...

Если нужно получить только первое совпадение, REGEXP_MATCH() (PostgreSQL 10+) является более эффективным.

4.6. REGEXP_REPLACE()

Функция REGEXP_REPLACE() заменяет подстроки, соответствующие шаблонам регулярных выражений POSIX, другим текстом. С PostgreSQL 15 добавлены параметры start и N для указания начальной позиции и номера вхождения для замены.

Синтаксис:

REGEXP_REPLACE(source TEXT, pattern TEXT, replacement TEXT, start INT DEFAULT 1, N INT DEFAULT 1, flags TEXT DEFAULT NULL)

• source: Исходная строка.

• pattern: Регулярное выражение для поиска.

• replacement: Строка, на которую нужно заменить совпадения. Может содержать \n для ссылки на n-е подвыражение в скобках или \& для всего совпадения.

• start: Начальная позиция для поиска. По умолчанию 1.

• N: Какое по счёту вхождение заменить. По умолчанию 1 (первое).

• flags: Флаги, управляющие поведением. Флаг g заменяет все* вхождения (игнорируется, если указан N).

Пример:

-- Заменить первое вхождение чисел на '*'
SELECT REGEXP_REPLACE(Email, '', '*') From Email;
-- Вывод:
-- regexp_replace
-- -------------------------
-- pratibha*7@sqlguide.edu
-- bhavyaa@pythonguide.com
-- ...

-- Заменить все вхождения чисел на '*'
SELECT REGEXP_REPLACE(Email, '', '*', 'g') From Email;
-- Вывод:
-- regexp_replace
-- -------------------------
-- pratibha**@sqlguide.edu
-- bhavyaa@pythonguide.com
-- ...

-- Удалить все нецифровые символы, чтобы остались только числа
SELECT REGEXP_REPLACE(Email, '\D', '', 'g') FROM Email;
-- Вывод:
-- regexp_replace
-- ----------------
-- 67
-- 23
-- ...

-- Удалить специальные символы
SELECT REGEXP_REPLACE(Email, '[^\w]+','','g') FROM Email;
-- Вывод:
-- regexp_replace
-- -----------------------
-- pratibha67sqlguideedu
-- bhavyaapythonguidecom
-- ...

4.7. REGEXP_SPLIT_TO_TABLE() и REGEXP_SPLIT_TO_ARRAY()

Эти функции разделяют строку, используя шаблон регулярного выражения POSIX в качестве разделителя.

1. REGEXP_SPLIT_TO_TABLE(): Возвращает результат в виде набора текстовых строк.

2. REGEXP_SPLIT_TO_ARRAY(): Возвращает результат в виде массива элементов типа TEXT.

Синтаксис:

REGEXP_SPLIT_TO_TABLE(string TEXT, pattern TEXT, flags TEXT DEFAULT NULL)

REGEXP_SPLIT_TO_ARRAY(string TEXT, pattern TEXT, flags TEXT DEFAULT NULL)

Пример:

-- Разбить строку по пробельным символам на отдельные строки
SELECT foo FROM REGEXP_SPLIT_TO_TABLE('the quick brown fox jumps over the lazy dog', '\s+') AS foo;
-- Вывод:
-- foo
-- -------
-- the
-- quick
-- brown
-- ...

-- Разбить строку по пробельным символам на массив
SELECT REGEXP_SPLIT_TO_ARRAY('the quick brown fox jumps over the lazy dog', '\s+');
-- Вывод:
-- regexp_split_to_array
-- -----------------------------------------------
-- {the,quick,brown,fox,jumps,over,the,lazy,dog}

5. Основные конструкции POSIX-регулярных выражений (ARE)

PostgreSQL по умолчанию использует расширенные регулярные выражения (ARE — Advanced Regular Expressions), которые представляют собой надмножество POSIX EREs и включают некоторые расширения, популярные в Perl и Tcl.

Основные элементы шаблонов:

• Атомы: Отдельные символы, группы символов, классы символов и т.д.

• Квантификаторы: Определяют количество повторений предыдущего атома.

• Ограничения: Соответствуют пустой строке при выполнении определённых условий (например, начало/конец строки/слова).

Основные конструкции:

1. . (точка): Соответствует любому одиночному символу (кроме новой строки в некоторых режимах).

2. [] (квадратные скобки): Определяют класс символов.

   1. [abc]: Любой из символов 'a', 'b', 'c'.

   2. [^abc]: Любой символ, кроме* 'a', 'b', 'c'.

   3. [a-z]: Диапазон символов от 'a' до 'z'.

   4. [[:digit:]], [[:space:]], [[:alnum:]], [[:word:]] и т.д.: Предопределённые классы символов.

3. | (вертикальная черта): Обозначает чередование (ИЛИ) между ветвями.

4. () (круглые скобки): Группируют элементы в одно логическое подвыражение.

   1. ([bc])\1: Пример обратной ссылки, \1 соответствует строке, захваченной первой группой ([bc]).

   2. (?:re): Группа без захвата (не фиксирует совпадение для regexp_matches).

   3. ^ (карет): Привязывает шаблон к началу строки.

5. $ (знак доллара): Привязывает шаблон к концу строки.

Квантификаторы. Определяют, сколько раз атом может повторяться:

• *: Ноль или более раз.

• +: Одно или более раз.

• ?: Ноль или один раз.

• {n}: Ровно n раз.

• {n,}: n или более раз.

• {n,m}: От n до m раз (включительно).

Жадные и нежадные квантификаторы:

По умолчанию квантификаторы являются жадными (greedy) — они пытаются сопоставить максимально возможную подстроку.

*, +, ?, {m,n} – жадные.

Чтобы сделать квантификатор нежадным (non-greedy), добавьте ? после него:

*?, +?, ??, {m,n}? – нежадные. Они сопоставляют минимально возможную подстроку.

Пример жадного/нежадного поведения:

SELECT SUBSTRING('XY1234Z', 'Y*({1,3})'); -- Жадный Y* съедает 'Y123', в скобках остается '123'
-- Вывод: 123
SELECT SUBSTRING('XY1234Z', 'Y*?({1,3})'); -- Нежадный Y*? съедает 'Y', в скобках остается '1'
-- Вывод: 1

Спецсимволы (экранированные последовательности):

• \d: Соответствует любой цифре (аналогично [[:digit:]] или ``).

• \s: Соответствует любому пробельному символу (аналогично [[:space:]]).

• \w: Соответствует любому «словесному» символу (буква, цифра, подчёркивание; аналогично [[:word:]] или [a-zA-Z0-9_]).

• \D: Соответствует любому нецифровому символу (аналогично [^[:digit:]]).

• \S: Соответствует любому непробельному символу (аналогично [^[:space:]]).

• \W: Соответствует любому не «словесному» символу (аналогично [^[:word:]]).

• \b: Символ «забой». Важно: в Perl-подобных regex часто означает границу слова, но здесь используется для backspace. Для границ слова см. ниже.

Спецсимволы-ограничения (границы):

• \A: Соответствует только началу строки (в отличие от ^, который может соответствовать началу каждой строки в многострочном режиме).

• \Z: Соответствует только концу строки (в отличие от $, который может соответствовать концу каждой строки в многострочном режиме).

• \m: Соответствует только началу слова.

• \M: Соответствует только концу слова.

• \y: Соответствует началу или концу слова.

• \Y: Соответствует позиции, не являющейся началом или концом слова.

Экранирование: Обратная косая черта (\) отменяет специальное значение следующего метасимвола. Если standard_conforming_strings выключен, \ в строковых константах нужно удваивать.

6. Практические сценарии и лучшие практики

Валидация форматов данных

Регулярные выражения идеально подходят для проверки соответствия данных определённому формату.

-- Найти пользователей с некорректными email-адресами
SELECT username, email
FROM users
WHERE email !~ '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$';

Пояснение: этот паттерн покрывает большинство «обычных» email; не претендует на полную стандартизацию RFC. Это быстрый фильтр «подозрительных» адресов.

Извлечение данных

Используйте REGEXP_SUBSTR() или REGEXP_MATCHES() для извлечения конкретных частей строк.

-- Извлечь доменное имя из email-адресов
SELECT DISTINCT SUBSTRING(Email FROM '.+@(.*)$') FROM Email;
-- Вывод:
--  substring
-- -----------------
--  sqlguide.edu
--  spguide.com
--  pythonguide.com
--  tsinfo.edu

Замена и очистка данных

REGEXP_REPLACE() незаменима для стандартизации и очистки строковых данных.

-- Стандартизировать номера телефонов, удалив нецифровые символы и добавив код страны
UPDATE contacts SET phone_number = '+7' || REGEXP_REPLACE(phone_number, '\D', '') WHERE REGEXP_LIKE(phone_number, '^\+?7?\d{10}$');

Поиск специфичных паттернов в логах

-- Найти записи логов об ошибке доступа для пользователей, начинающихся с 'admin_' и заканчивающихся цифрами
SELECT log_id, log_entry FROM system_logs WHERE REGEXP_LIKE(log_entry, 'Access denied for user \'admin_\w*\d+\'', 'i');

7. Производительность регулярных выражений

Регулярные выражения, хотя и мощны, могут быть ресурсоёмкими, особенно на больших объёмах данных.

Советы по оптимизации:

• Индексы не работают с регулярными выражениями. Если возможно, используйте дополнительные условия в WHERE, которые могут задействовать индексы, чтобы сократить выборку перед применением regex. (На самом деле это немного не так, мы обсудим этот вопрос чуть ниже.)

• Ограничивайте выборку: Применяйте другие фильтры, чтобы минимизировать количество строк, обрабатываемых регулярными выражениями.

• Используйте более конкретные шаблоны: Это уменьшает количество проверок и ускоряет поиск.

• Будьте осторожны с шаблонами из недоверенных источников: Сложные регулярные выражения могут потреблять значительное время и память. В таких случаях рекомендуется использовать тайм-аут запроса (statement timeout).

8. Производительность регулярных выражений и использование индексов

Регулярные выражения — мощный, но зачастую медленный инструмент. Их выполнение требует полного перебора строки (scan), и они плохо поддаются оптимизации стандартными методами. Понимание того, как разные операторы взаимодействуют с индексами, критически важно для производительности.

8.1. Поддержка индексов для разных типов операторов

1. POSIX-операторы (~, ~*, !~, !~*)

   • Индексы НЕ используются. Эти операторы реализованы как функции, и система не может заранее предсказать их результат для построения плана по индексу. Запрос всегда будет выполняться через последовательное сканирование (Sequential Scan).

   • Пример:

     -- Этот запрос НЕ будет использовать индекс по column_text
     EXPLAIN ANALYZE SELECT * FROM my_table WHERE column_text ~ '^abc.*xyz$';
     -- В плане будет: "Seq Scan on my_table"
     

2. Оператор SIMILAR TO

   • Индексы МОГУТ использоваться, но с огромными оговорками. Так как SIMILAR TO имеет ограниченный синтаксис (по сути, это комбинация LIKE с регулярными выражениями), оптимизатор иногда может преобразовать шаблон, начинающийся с простого префикса, в поиск по индексу.

   • На практике это происходит очень редко. Любой нетривиальный шаблон, особенно начинающийся с метасимвола (например, %), снова приведет к последовательному сканированию.

   • Пример, где индекс может быть использован:

     -- Есть ШАНС, что этот запрос использует индекс, если он есть по column_text
     EXPLAIN ANALYZE SELECT * FROM my_table WHERE column_text SIMILAR TO 'abc%';
     

3. Операторы LIKE и ILIKE (сравнение по шаблону)

   • Индексы МОГУТ использоваться для шаблонов с фиксированным префиксом. Это ключевое отличие.

   • Работает: WHERE column_text LIKE 'abc%' (поиск по префиксу). Созданный стандартный B-tree индекс по column_text будет использоваться.

   • Не работает: WHERE column_text LIKE '%xyz' (поиск по суффиксу). Индекс не поможет.

8.2. Расширение pg_trgm — ключ к производительности

Для ускорения не только LIKE/ILIKE с любыми шаблонами (включая %text%), но и некоторых регулярных выражений, существуют специальные Trigram Indexes (индексы по триграммам). Для их использования необходимо включить расширение pg_trgm. Этот тип индекса разбивает текст на последовательности из 3 символов и идеально подходит для нечеткого поиска и поиска по подстрокам.

Подключение и создание индекса:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- GIN — часто быстрее для чтения
CREATE INDEX ON my_table USING gin (column_text gin_trgm_ops);

-- GiST — может быть выгоднее для частых обновлений
CREATE INDEX ON my_table USING gist (column_text gist_trgm_ops);

Когда индекс на основе pg_trgm помогает, а когда — нет:

• Индекс помогает, если из шаблона можно извлечь фиксированные подстроки, которые определяют триграммы.

  • email ILIKE '%@example.com' — фиксированная подстрока @ex, exa, xam, ...

  • email ~ '^[A-Za-z0-9._%+-]+@example\.com$' — есть фиксированная подстрока @example.com

• Индекс не поможет, если шаблон слишком «свободен» (например, .*foo.*bar.* без фиксированных частей) или начинается с неопределённого квантификатора. В таком случае движок не может предсказать триграммы → full scan.

Практические рекомендации по индексации:

• Создавайте индексы только после анализа паттернов запросов.

• Рассмотрите выраженные индексы, например, для регистронезависимого поиска:

  CREATE INDEX ON users USING gin (lower(email) gin_trgm_ops);

• Для больших таблиц тестируйте индексы на рабочих данных с помощью EXPLAIN ANALYZE.

8.3. Практические советы по оптимизации запросов

1. Сначала сузить выборку: Всегда старайтесь сначала отфильтровать данные с помощью условий, которые могут использовать индексы (равенство, диапазон дат, префиксный LIKE), и только затем применяйте REGEXP_ функции к оставшемуся небольшому подмножеству строк. Плохо:

   SELECT * FROM huge_table WHERE REGEXP_SUBSTR(description, 'complex|pattern') IS NOT NULL;

   Лучше:

   SELECT * FROM huge_table 
   WHERE category_id = 100 -- Это условие использует индекс
     AND REGEXP_SUBSTR(description, 'complex|pattern') IS NOT NULL; -- Применяется к малому кол-ву строк

2. Избегайте "жадных" и сложных шаблонов: Такие конструкции, как .*, (.+)*, вложенные квантификаторы, могут привести к "катастрофическому backtracking", когда время выполнения выражения растет экспоненциально с ростом длины строки. Будьте как можно более конкретны.

3. Устанавливайте statement_timeout: Для запросов, использующих регулярные выражения из ненадёжных источников (например, вводимые пользователем), обязательно устанавливайте таймаут на уровне транзакции или оператора, чтобы "тяжелый" шаблон не подвесил всю базу.

   SET LOCAL statement_timeout = '5s'; -- Таймаут только для текущей транзакции
   SELECT * FROM table WHERE some_column ~ user_input_pattern;

Вывод: Для максимальной производительности используйте регулярные выражения POSIX только когда это действительно необходимо. Во всех остальных случаях старайтесь заменить их на комбинацию LIKE с триграмными индексами или простые строковые функции (strpos, substring). Всегда используйте EXPLAIN ANALYZE для анализа плана выполнения ваших запросов.

9. Безопасность: ReDoS (Regular Expression Denial of Service) и превентивные меры

Некорректно составленные regex могут привести к экспоненциальному времени обработки при определённых входных данных (особенно при множественных вложенных квантификаторах и ветвлениях). Это ReDoS — реальная угроза в публичных API, где пользователи передают свои паттерны.

Как защититься:

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

2. Ограничивайте размер входной строки. Например, WHERE length(text) <= 5000 перед применением regex.

3. Используйте statement_timeout для сессий/запросов, где возможны медленные вычисления:

SET LOCAL statement_timeout = '5s';

Или настройте на уровне приложения/connection pool.

4. Проверяйте длинные или сложные шаблоны в тестовой среде, анализируйте потенциальные временные затраты.

5. Логируйте медленные запросы и реагируйте, если видите аномально долгие регулярки.

Заключение

Регулярные выражения в PostgreSQL — это невероятно гибкий и мощный инструмент для работы с текстовыми данными. От простых операторов LIKE до продвинутых функций REGEXP_SUBSTR и REGEXP_REPLACE, они позволяют решать широкий круг задач по поиску, валидации, извлечению и преобразованию строк. Понимание их синтаксиса и особенностей использования в PostgreSQL значительно расширяет возможности любого инженера по работе с базой данных. Помни о версиях функций (особенно PostgreSQL 15+) и учитывай особенности производительности при работе с большими наборами данных.