invalid_regular_expression invalid regular expression: … — 2201B

Symptoms

A regular expression passed to ~, regexp_match, regexp_replace, or similar is malformed. PostgreSQL raises SQLSTATE 2201B (invalid_regular_expression) and describes the problem.

• The DETAIL names the specific regex fault (e.g. unmatched parenthesis).
• Often from user-supplied patterns or escaping mistakes.
• Affects POSIX regex operators and functions.

What the server log shows

ERROR:  invalid regular expression: parentheses () not balanced

Why PostgreSQL raises this — what the manual says

Section 9.7.3 POSIX Regular Expressions:

“A regular expression is a character sequence that is an abbreviated definition of a set of strings (a regular set).”

PostgreSQL compiles the pattern before matching. Structural faults — unbalanced parentheses/brackets, dangling quantifiers, bad escapes — fail compilation and produce 2201B.

Common causes

• Unbalanced () or [] in the pattern.
• Invalid escape sequences or quantifiers.
• Unescaped user input treated as a pattern.

How to fix it

1. Fix the pattern syntax (balance brackets, escape special characters).
2. Escape literal user input with regexp_replace or treat it as a literal via position()/LIKE.
3. Test patterns with SELECT 'x' ~ 'your-pattern'; before deploying.

Related & next steps

• SQLSTATE 2201B — Invalid Regular Expression (full reference)
• Message: LIKE pattern must not end with escape character
• Glossary: Regular expression

Reference: PostgreSQL 18 Section 9.7 “Pattern Matching”.