REGEXP_MATCHES no PostgreSQL: grupos de captura e o flag g

REGEXP_MATCHES no PostgreSQL extrai de uma string as correspondencias de uma expressao regular e retorna os grupos capturados como um array text[]. Nao e uma funcao escalar: sem flags ela entrega uma linha de resultado por chamada, e com o flag g entrega uma linha por correspondencia. Entender essa natureza de funcao que retorna conjuntos mata metade dos bugs misteriosos.

O caso basico: grupos em um array

A funcao retorna um array com os grupos capturados. Se o padrao nao tem grupos, a correspondencia inteira cai no array. Para tirar um valor, indexe o array ([1] e o primeiro grupo):

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

Uma tarefa classica e extrair um id numerico de um texto. Os parenteses definem o grupo e \d+ captura uma sequencia de digitos:

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

A grande pegadinha: sem correspondencia, sem linha

REGEXP_MATCHES se comporta como um INNER JOIN, nao como uma expressao escalar. Quando nada corresponde, a funcao retorna zero linhas e a linha da tabela some silenciosamente do resultado. Nao e um NULL: o registro simplesmente desaparece.

-- PERIGO: usuarios sem digitos no nome somem do resultado
SELECT id, (REGEXP_MATCHES(name, '(\d+)'))[1] AS digits
FROM users;

• Se voce precisa de toda linha da tabela, nao chame a funcao diretamente no SELECT.
• A alternativa segura e regexp_substr (PostgreSQL 15+), que retorna NULL:

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

Ou envolva em um LEFT JOIN LATERAL para preservar as linhas sem correspondencia.

O flag g: uma linha por correspondencia

O terceiro argumento sao os flags. O flag g (global) transforma a funcao em um gerador: uma string de entrada produz tantas linhas quantas forem as correspondencias. Isso brilha ao parsear listas e tokens:

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

Para juntar os tokens de volta em um array por pedido, combine com um agregado ou com 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;

Outros flags uteis: i para ignorar maiusculas e minusculas, n para que o ponto nao cruze quebras de linha.

REGEXP_MATCHES versus regexp_substr

Escolha a ferramenta certa para a tarefa:

• REGEXP_MATCHES quando voce precisa de todos os grupos ou de todas as correspondencias (o flag g). Retorna um array e descarta as linhas sem correspondencia.
• regexp_substr quando voce precisa de uma substring e tem de manter a linha: retorna NULL em vez de descartar o registro.

-- Extrair o dominio de cada usuario sem perder linhas
SELECT id,
       email,
       regexp_substr(email, '@(.+)$', 1, 1, '', 1) AS domain
FROM users;

O MySQL 8 nao tem REGEXP_MATCHES; o analogo mais proximo e REGEXP_SUBSTR(col, pattern) (sem captura de grupos antes da 8.0.x) ou REGEXP_REPLACE para extrair. No ClickHouse use extractAll(s, pattern) (equivalente ao flag g) e extract(s, pattern) para um unico grupo. Lembre-se: a semantica de "sem correspondencia, sem linha" e exclusiva do REGEXP_MATCHES e e o que mais costuma quebrar relatorios.