Регулярные выражения в PostgreSQL

Приветствую всех, читателей! Эта статья предназначена для специалистов уровня 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+) и учитывай особенности производительности при работе с большими наборами данных.