Incident brief
Invalid regular expression
A regular expression pattern passed to a PostgreSQL function was not valid — usually unbalanced parentheses or brackets. PostgreSQL refused to compile it.
In 10 seconds
- What
- Invalid regular expression
- What triggers it
- Call substring(... from pattern) with a pattern containing an unmatched opening parenthesis.
- The fix
- Balance every ( and [ in the pattern with its matching ) or ].
- Proof
- Reproduced on PostgreSQL 16.14 → Calling substring with an unbalanced-parenthesis pattern was rejected with SQLSTATE 2201B, naming the exact syntax problem. The session was unaffected afterward.
The fix
What to do right now
The immediate, application-level response to this error.
- Balance every ( and [ in the pattern with its matching ) or ].
- If you want to match a literal '(' or ')' character rather than group with it, escape it with a backslash.
- Test regex patterns against a few real sample strings before shipping them, ideally with a fixed, non-fabricated example (see the premium test).
SELECT substring('(foo)' from '\(foo\)');Diagnose
See it live on the server
Run these against the affected instance to confirm the diagnosis before you act.
Standard triage — not specific to this error
These are canonical PostgreSQL system-catalog queries, shown as SQL to run. No sample output is attached because this is general triage, not a captured lab transcript.This SQLSTATE does not have an error-specific live snapshot yet. These are the canonical system-catalog queries you run against the affected server to see the problem in real time — standard triage, not a reproduced transcript.
What is running right now
Active backends, how long each has been running, and what it is waiting on.
SELECT pid,
state,
wait_event_type,
wait_event,
now() - query_start AS running_for,
left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
AND pid <> pg_backend_pid()
ORDER BY running_for DESC NULLS LAST;Who is blocking whom
Turn raw blocking PIDs into the actual queries on both sides of the wait.
SELECT blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query
FROM pg_stat_activity AS blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN pg_stat_activity AS blocking ON blocking.pid = b.pid
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;Locks that are still waiting
Every lock a backend has requested but not yet been granted.
SELECT l.pid,
l.locktype,
l.mode,
l.granted,
COALESCE(c.relname, l.transactionid::text) AS object
FROM pg_locks l
LEFT JOIN pg_class c ON c.oid = l.relation
WHERE NOT l.granted
ORDER BY l.pid;Why it happens
What PostgreSQL is telling you
The mechanism behind the error, grounded in the official manual — not paraphrased.
PostgreSQL 16 Documentation — POSIX Regular Expressions
if the pattern contains any parentheses, the portion of the text that matched the first parenthesized subexpression ... is returned. You can put parentheses around the whole expression if you want to use parentheses within it without triggering this exception.Read the full section on postgresql.org →
The invalid pattern
A single unmatched '(' is not a valid regular expression — PostgreSQL's regex compiler needs every opening parenthesis matched by a closing one, so it rejects the pattern before running it against any data.Confirming the session still works
The rejected pattern didn't affect the session — the next statement ran normally.Reproduce & verify
A real, single-session PostgreSQL reproduction
A literal transcript of SQL run against a live PostgreSQL instance in an isolated lab — the commands below are exactly what was executed.
- 1Call substring(... from pattern) with a pattern containing an unmatched opening parenthesis.
- 2PostgreSQL's regular expression compiler cannot parse the pattern.
- 3SQLSTATE 2201B is reported, naming the specific syntax problem.
One client: call substring() with a pattern that has an unmatched opening parenthesis.
-- No table needed: the failure happens purely inside the regex compiler.
SELECT 'no schema required' AS setup_note;SELECT substring('foo' from '(');SELECT 1 AS session_still_alive;What PostgreSQL actually returned
setup_note
--------------------
no schema required
(1 row)ERROR: invalid regular expression: parentheses () not balanced session_still_alive
----------------------
1
(1 row)The fix (escaping literal parentheses) was tested directly against the exact string this error is usually reached for — text that legitimately contains parenthesis characters.
Escape the literal parentheses in both the search string and the pattern, then extract the text.
Without this
Above: an unescaped, unbalanced '(' is rejected with SQLSTATE 2201B.
With this, tested
Below: escaping the literal parentheses with backslashes compiles and extracts the exact text.
What Pro unlocks here
- The exact prevention SQL — copy-paste ready
- Raw psql output captured from the Docker lab
- A senior-DBA action list to take it further
- Live monitoring queries to catch it in production
- The deeper audit: fix-that-fails counterexample, GUC before/after, server-log evidence
Related & next steps
Follow the thread
Everything this error touches — jump straight to the sibling error, term, runbook, or parameter.
Verification
- Last verified
- 2026-07-16 (Docker lab, PostgreSQL 16.14)
- Reviewed by
- Verified against PostgreSQL 16.14 in an isolated lab environment
- Audit status
- reviewed