REGEXP_MATCHES in PostgreSQL: Capture Groups and the g Flag

REGEXP_MATCHES в PostgreSQL извлекает из строки совпадения с регулярным выражением и возвращает захваченные группы как массив text[]. Это не скалярная функция: без флага она отдаёт одну строку результата на первое совпадение, а с флагом g — по строке на каждое совпадение. Понимание этой «строковозвращающей» природы избавит вас от половины загадочных багов.

Извлечение групп нужно, когда одно поле упаковывает несколько смысловых частей: order-4815 несёт код заказа и числовой id, e-mail — локальную часть и домен, строка лога — уровень, метку времени и сообщение. Каждая пара скобок в шаблоне становится отдельным элементом возвращаемого массива, и порядок групп слева направо задаёт порядок индексов: [1] — первая группа, [2] — вторая. Если групп в шаблоне нет вовсе, в массив попадает целиком найденная подстрока. Поэтому REGEXP_MATCHES берут именно там, где из одной колонки нужно вытащить сразу несколько полей одним проходом регулярного выражения, а не там, где достаточно одной подстроки.

Базовый случай: группы в массиве

Функция возвращает массив с захваченными группами. Если групп нет, в массив попадает всё совпадение целиком. Чтобы достать значение, индексируйте массив ([1] — первая группа):

SELECT (REGEXP_MATCHES(email, '^([^@]+)@(.+)$'))[1] AS local_part,
       (REGEXP_MATCHES(email, '^([^@]+)@(.+)$'))[2] AS domain
FROM users;

Типичная задача — выдернуть числовой id из текста. Скобки задают группу, \d+ — последовательность цифр:

SELECT id,
       (REGEXP_MATCHES(name, 'order-(\d+)'))[1] AS order_no
FROM orders
WHERE name ~ 'order-\d+';

Обратите внимание на пару скобок вокруг функции: REGEXP_MATCHES(...) возвращает массив, и индексация [1] применяется именно к нему, поэтому вызов приходится заключать в скобки. Без индекса в выборку попал бы весь массив целиком — например {4815} или {john,example.com}, — а это редко то, что нужно дальше по запросу. Результат группы всегда имеет тип text, даже когда вы захватили цифры: чтобы сравнивать order_no как число или класть в целочисленную колонку, приведите его явно через ::int.

Главная ловушка: нет совпадения — нет строки

REGEXP_MATCHES ведёт себя как INNER JOIN, а не как скалярное выражение. Если совпадения нет, функция возвращает ноль строк, и строка таблицы молча выпадает из результата — это не NULL, это исчезновение записи.

-- DANGER: users with no digits in the name just disappear
SELECT id, (REGEXP_MATCHES(name, '(\d+)'))[1] AS digits
FROM users;

• Если нужна каждая строка таблицы — не вызывайте функцию в SELECT напрямую.
• Безопасный вариант — regexp_substr (PostgreSQL 15+), который возвращает NULL:

SELECT id, regexp_substr(name, '\d+') AS digits
FROM users;

Либо оборачивайте в LEFT JOIN LATERAL, чтобы сохранить непросопоставленные строки: LATERAL подставляет результат функции к каждой строке таблицы, а LEFT подставляет NULL там, где функция не вернула ничего. Эта же ловушка коварна тем, что на счастливых данных запрос выглядит исправным — пока в таблицу не попадёт первая строка без цифр в имени, и тогда счётчик строк в отчёте молча уменьшится без всякой ошибки.

Флаг g: по строке на каждое совпадение

Третий аргумент — флаги. Флаг g (global) превращает функцию в генератор: одна входная строка порождает столько строк, сколько совпадений. Это удобно для разбора списков и токенов:

SELECT id,
       (REGEXP_MATCHES(status, '(\w+)', 'g'))[1] AS token
FROM orders;

Каждое совпадение по-прежнему приходит массивом, поэтому индекс [1] нужен и здесь: он достаёт первую группу из каждой строки-совпадения. Учтите, что g меняет кардинальность выборки — заказ с тремя токенами даст три строки, а соединять такой результат с другими таблицами нужно осторожно, иначе значения из родительской строки задублируются. Чтобы собрать токены обратно в массив на каждый заказ, комбинируйте с агрегатом или LATERAL:

SELECT o.id,
       array_agg(m.token) AS tokens
FROM orders o
CROSS JOIN LATERAL (
  SELECT (REGEXP_MATCHES(o.status, '([a-z]+)', 'g'))[1] AS token
) AS m
GROUP BY o.id;

Без флага g REGEXP_MATCHES находит только первое вхождение и возвращает одну строку, так что разница между «первый токен» и «все токены» сводится ровно к этому третьему аргументу. Флаги можно комбинировать в одной строке: 'gi' ищет все совпадения без учёта регистра. Другие полезные флаги: i — без учёта регистра, n — точка не совпадает с переводом строки.

REGEXP_MATCHES против regexp_substr

Выбирайте инструмент под задачу:

• REGEXP_MATCHES — когда нужны все группы или все совпадения (флаг g). Возвращает массив, фильтрует строки без совпадения.
• regexp_substr — когда нужна одна подстрока и важно сохранить строку: возвращает NULL вместо исчезновения записи.

-- Extract the domain for every user without losing rows
SELECT id,
       email,
       regexp_substr(email, '@(.+)$', 1, 1, '', 1) AS domain
FROM users;

Здесь regexp_substr берёт пятый и шестой аргументы — индекс совпадения и номер группы (1 означает первую группу), поэтому домен возвращается без обёртывающего массива и без потери строк, у которых символа @ нет. У REGEXP_MATCHES такого режима нет: даже одна группа всегда приходит элементом массива, который надо распаковать индексом, и любая строка без совпадения молча исчезает из выборки. Когда колонку фильтруют по этому же шаблону, помните, что REGEXP_MATCHES в списке SELECT совмещает фильтрацию и извлечение, а флаг g ещё и размножает строки, — так одно выражение незаметно меняет и состав, и количество строк в результате.

В MySQL 8 нет REGEXP_MATCHES; ближайший аналог — REGEXP_SUBSTR(col, pattern) (без захвата групп до версии 8.0.x) либо REGEXP_REPLACE для извлечения. В ClickHouse используйте extractAll(s, pattern) (аналог флага g) и extract(s, pattern) для одной группы. Помните: семантика «нет совпадения — нет строки» уникальна для REGEXP_MATCHES, и именно она чаще всего ломает отчёты.