EC
2201B
PostgreSQLERRORNotableData ExceptionHIGH confidence

invalid regular expression

What this means

SQLSTATE 2201B is raised when a regular expression pattern passed to ~ (regex match), regexp_match, regexp_replace, or similar functions is syntactically invalid and cannot be compiled.

Why it happens
  1. 1A malformed regular expression with unclosed parentheses, invalid quantifiers, or other syntax errors
  2. 2Using PCRE-only syntax (e.g., lookaheads) in Postgres which uses POSIX ERE
How to reproduce

Regex match with a syntax error in the pattern.

trigger — this will error
trigger — this will error
SELECT 'hello' ~ '(unclosed';

expected output

ERROR:  invalid regular expression: parentheses () not balanced

Fix 1

Fix the regex syntax error

WHEN When a hand-authored regex fails.

Fix the regex syntax error
SELECT 'hello' ~ '(closed)';

Why this works

Ensure all parentheses, brackets, and quantifiers are correctly balanced and use POSIX ERE syntax supported by Postgres.

Fix 2

Test regex patterns before deploying

WHEN When patterns are dynamically built from user input.

Test regex patterns before deploying
-- Wrap in a BEGIN/EXCEPTION block to catch 2201B and return NULL:
DO $
BEGIN
  PERFORM 'test' ~ :'user_pattern';
EXCEPTION WHEN invalid_regular_expression THEN
  RAISE NOTICE 'Bad pattern';
END $;

Why this works

Catching 2201B in PL/pgSQL lets you validate user-supplied patterns without crashing the query.

What not to do

✕ Pass unsanitised user input as a regex pattern

An invalid pattern raises 2201B and aborts the query; a valid but complex pattern can cause catastrophic backtracking.

Sources
Official documentation ↗

Class 22 — Data Exception

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All PostgreSQL errors