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.
What lands in your log
ERROR: negative substring length not allowed
In 10 seconds
- What triggers it
- Call substring() with a FOR length that evaluates to a negative number.
- 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.
Fix
What to do right now
Application-level steps for 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;For this error
See this error live on the server
Run these against the affected instance to confirm the diagnosis before you act.
Negative substring length not allowed (22011) is statement-local: PostgreSQL does not retain the rejected value after the statement ends. Capture the exact bound value and statement position in application/server logs, then run this targeted validation before retrying.
Validate substring bounds before execution
Validate the specific value/shape that can raise this SQLSTATE.
SELECT start_pos, requested_length,
requested_length < 0 AS would_raise_22011
FROM (VALUES (1, -1)) v(start_pos, requested_length);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.
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.
- A second operational test: exact SQL, raw output, measured result, and engineer notes
Card required. Cancel before day 7 and you are not charged.
Connected
Everything this error touches
Every page this SQLSTATE connects to: the concept that explains it, the runbooks that fix it, the parameters you tune to prevent it, and the sibling errors it travels with. All real cross-references. Jump straight in, or open the full interactive map.
Related errors
Verification
- Last verified
- 2026-07-19 (isolated lab, PostgreSQL 16.14)
- Verification scope
- Verified against PostgreSQL 16.14 in an isolated lab environment
- Audit status
- reviewed
Went further?
Pro unlocks the second lab proof
Free page stops the bleeding. Pro adds the operational test, SQLSTATE audit, and deeper evidence, same error, more certainty.