PostgreSQL Substring Error (SQLSTATE 22011)

When a SUBSTRING call receives arguments that violate SQL standard rules, PostgreSQL raises:

ERROR:  negative substring length not allowed
SQLSTATE: 22011

The condition name is substring_error, and it belongs to SQLSTATE class 22 — Data Exception. This error surfaces when the length argument to SUBSTRING is negative or otherwise invalid per the SQL standard.

What This Error Means

SQLSTATE class 22 covers errors that arise from invalid data values, not from syntax or schema problems. Within that class, 22011 specifically targets string-function argument violations in SUBSTRING (and its SQL-standard form SUBSTRING(string FROM start FOR length)).

PostgreSQL raises this error synchronously during query execution when it evaluates the SUBSTRING expression with a negative length. The SQL standard forbids a negative length argument in SUBSTRING(string FROM start FOR length) — the length operand must be a non-negative integer. PostgreSQL enforces this strictly.

The error causes the current statement to fail. In an explicit transaction block, the transaction is placed in an aborted state and all subsequent commands will fail until you issue ROLLBACK (or ROLLBACK TO SAVEPOINT if you used a savepoint). Outside a transaction block, only the single statement is rolled back.

Note that SUBSTRING with only a start position and no length argument (SUBSTRING(string FROM start)) does not trigger this error — it simply returns the rest of the string from that position.

Common Causes

1. Passing a negative integer literal as the length argument. For example: SUBSTRING('hello' FROM 2 FOR -1) — the FOR -1 part violates the standard and raises 22011.

2. Computing the length from a formula that can go negative. A common pattern is SUBSTRING(col FROM pos FOR end_pos - pos). If end_pos is less than pos, the computed length is negative and the error fires at runtime even though the query looks correct statically.

3. User-supplied input passed directly into the length argument. If application code accepts a user-provided length and passes it without validation, a negative or zero value can reach the database.

4. Off-by-one arithmetic in dynamic SQL. Stored procedures or functions that compute substring boundaries from column data can produce a negative length when the source data does not match the expected format.

How to Fix substring_error

1. Guard the length with GREATEST(0, ...).
   Wrap the computed length so it never goes below zero:

   -- Instead of this:
   SELECT SUBSTRING(col FROM start_pos FOR end_pos - start_pos) FROM t;
   
   -- Use this:
   SELECT SUBSTRING(col FROM start_pos FOR GREATEST(0, end_pos - start_pos)) FROM t;
   

   A length of 0 returns an empty string, which is usually the correct behavior when the range is empty.

2. Add an explicit check before calling SUBSTRING.
   Use a CASE expression to short-circuit when the length would be negative:

   SELECT
     CASE
       WHEN end_pos > start_pos
         THEN SUBSTRING(col FROM start_pos FOR end_pos - start_pos)
       ELSE ''
     END
   FROM t;
   

3. Validate inputs in application code before they reach SQL.
   If the length comes from user input or an API parameter, enforce length >= 0 at the application layer before constructing the query.

4. Use the two-argument form when you only need a start offset.
   If you do not actually need to limit the length, omit the FOR length clause entirely:

   -- Returns everything from position 3 onward — no length restriction:
   SELECT SUBSTRING(col FROM 3) FROM t;
   

5. Use SUBSTR for zero-based or length-optional slicing.
   The SUBSTR(string, start, length) function is an alias that behaves identically to SUBSTRING ... FROM ... FOR ... and raises the same error for a negative length, but is sometimes clearer in intent. The fix is the same.

6. Catch the error in PL/pgSQL with an exception handler when the negative length is an expected edge case:

   DO $$
   DECLARE
     result text;
   BEGIN
     result := SUBSTRING('hello' FROM 3 FOR -1);
   EXCEPTION
     WHEN substring_error THEN
       result := '';
   END;
   $$;
   

Additional Information

• SQLSTATE 22011 is part of class 22 (Data Exception). Related conditions in the same class include 22001 (string_data_right_truncation), 22003 (numeric_value_out_of_range), and 22012 (division_by_zero).
• The SQL standard (ISO/IEC 9075) defines the substring_error condition. PostgreSQL has conformed to this behavior since early versions; there is no version-specific change to be aware of for production PostgreSQL (9.x and later).
• The condition name substring_error can be used directly in PL/pgSQL EXCEPTION WHEN clauses without knowing the SQLSTATE code.
• Most PostgreSQL drivers (libpq, psycopg2, asyncpg, JDBC) surface this as a DataError or equivalent class, with the SQLSTATE 22011 available in the exception object.
• ORMs such as SQLAlchemy will raise sqlalchemy.exc.DataError wrapping the original 22011 message. The original message (negative substring length not allowed) is typically included in the exception string.

Frequently Asked Questions

Why does PostgreSQL reject a negative length when other databases silently return an empty string?
PostgreSQL follows the SQL standard strictly: the standard specifies that a negative length argument is an error. Some databases (e.g., MySQL's SUBSTRING) treat a negative or zero length as returning an empty string silently. If you are migrating SQL from MySQL to PostgreSQL, this difference can cause runtime failures that were not visible before.

Does this error only apply to the SUBSTRING function?
Yes. SQLSTATE 22011 is specifically tied to the SUBSTRING / SUBSTRING ... FROM ... FOR ... family. Other string functions that receive invalid arguments raise different SQLSTATE codes (for example, REPEAT with a negative count returns an empty string rather than an error, and OVERLAY with a negative length raises 22011 as well since it uses similar SQL-standard substring mechanics internally).

Can I reproduce this error deterministically for testing?
Yes:

SELECT SUBSTRING('test' FROM 1 FOR -1);
-- ERROR:  negative substring length not allowed
-- SQLSTATE: 22011

Will wrapping in GREATEST(0, length) affect performance?
No. The GREATEST function on two integer arguments is essentially free — it compiles to a single comparison instruction. There is no meaningful performance difference compared to the unguarded call.