Incident brief
Negative substring length not allowed
substring(... FOR count) was given a negative count. PostgreSQL treats a negative substring length as a data error rather than returning an empty string.
In 10 seconds
- What
- Negative substring length not allowed
- What triggers it
- Call substring() with a FOR length that evaluates to a negative number.
- The fix
- Clamp the length to zero with GREATEST(len, 0) so a computed negative can never reach substring().
- Proof
- Reproduced on PostgreSQL 16.14 → substring('postgresql' FROM 3 FOR -2) was rejected with SQLSTATE 22011. Clamping the length to zero with GREATEST returned an empty string instead of erroring.
The fix
What to do right now
The immediate, application-level response to this error.
- Clamp the length to zero with GREATEST(len, 0) so a computed negative can never reach substring().
- Validate or recompute the length in application code before building the query.
- Prefer left()/right() when you actually want a fixed number of leading or trailing characters.
-- clamp a possibly-negative length to zero instead of raising 22011
SELECT substring('postgresql' FROM 3 FOR GREATEST(-2, 0)) AS safe_empty;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 — §9.4 String Functions and Operators
Extracts the substring of string starting at the start'th character if that is specified, and stopping after count characters if that is specified. Provide at least one of start and count.Read the full section on postgresql.org →
The negative-length substring call
The FOR value is -2. A substring cannot have a negative length, so PostgreSQL raises SQLSTATE 22011 rather than silently returning an empty result.The session is unaffected by the error
The failure was a scalar data error outside any transaction, so the connection is fine and the next query runs 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() with a FOR length that evaluates to a negative number.
- 2PostgreSQL raises SQLSTATE 22011 instead of returning a result.
- 3Outside a transaction, the session is unaffected — the next statement runs normally.
One client, no schema: a substring() call on a string literal with a negative FOR length.
-- substring() on a literal is self-contained; no tables required.
SELECT 'no schema needed' AS setup_note;SELECT substring('postgresql' FROM 3 FOR -2);SELECT length('postgresql') AS full_length;What PostgreSQL actually returned
setup_note
------------------
no schema needed
(1 row)ERROR: negative substring length not allowed full_length
-------------
10
(1 row)GREATEST(len, 0) was tested against both the negative length and a correct positive length, proving the clamp prevents 22011 while a valid length still returns the right characters.
Run the same substring() twice: once with the length clamped to zero, once with a correct positive length.
Without this
Above: a raw negative FOR length raises SQLSTATE 22011.
With this, tested
Below: GREATEST clamps the negative to zero (empty string); a valid length returns the characters.
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-19 (isolated lab, PostgreSQL 16.14)
- Reviewed by
- Verified against PostgreSQL 16.14 in an isolated lab environment
- Audit status
- reviewed