2201BERRORTier 2 — Caution✅ HIGH confidence

invalid regular expression

Category: Data ExceptionVersions: All Postgres versions

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

1A malformed regular expression with unclosed parentheses, invalid quantifiers, or other syntax errors
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

SELECT 'hello' ~ '(unclosed';

ERROR: invalid regular expression: parentheses () not balanced

Fix 1: Fix the regex syntax error

When a hand-authored regex fails.

fix

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 patterns are dynamically built from user input.

fix

-- 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

Why it's wrong: An invalid pattern raises 2201B and aborts the query; a valid but complex pattern can cause catastrophic backtracking.

Sources

📚 Official docs: https://www.postgresql.org/docs/current/errcodes-appendix.html

📚 Feature docs: https://www.postgresql.org/docs/current/functions-matching.html

🔧 Source ref: Class 22 — Data Exception

Confidence assessment

✅ HIGH confidence

Standard SQLSTATE for regex compilation errors. Stable across versions.