PostgreSQL 22011 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22011: substring error

PostgreSQL error code 22011 (substring_error) occurs when a string function like SUBSTRING or OVERLAY receives an invalid parameter, most commonly a negative length value. This error is strictly enforced by the SQL standard, which mandates that substring lengths must be zero or greater. It frequently surfaces in ETL pipelines, dynamic SQL applications, or anywhere user-supplied values are passed directly into string functions.

---

Top 3 Causes

1. Passing a Negative Length to SUBSTRING

The most common cause is supplying a negative integer to the FOR length clause of the SUBSTRING function.

-- Triggers error 22011
SELECT SUBSTRING('Hello World' FROM 1 FOR -3);
-- ERROR:  negative substring length not allowed
-- SQLSTATE: 22011

-- Also triggers in standard syntax
SELECT SUBSTRING('Hello World', 1, -3);
-- ERROR:  negative substring length not allowed

2. Dynamically Calculated Length Becomes Negative

When computing length based on column data, the result can silently become negative when the column value is shorter than expected.

-- If name has fewer than 10 characters, this fails
SELECT SUBSTRING(name FROM 1 FOR LENGTH(name) - 10)
FROM employees
WHERE department = 'HR';
-- Rows where LENGTH(name) < 10 will throw 22011

3. Invalid Parameters in OVERLAY Function

The OVERLAY function shares the same error class and will throw 22011 when the FOR length argument is negative.

-- Triggers error 22011 in OVERLAY
SELECT OVERLAY('Hello World' PLACING 'DB' FROM 1 FOR -2);
-- ERROR:  negative substring length not allowed
-- SQLSTATE: 22011

---

Quick Fix Solutions

Use GREATEST(0, length) to clamp the value:

-- Safe: GREATEST ensures length is never negative
SELECT SUBSTRING('Hello World' FROM 1 FOR GREATEST(0, -3));
-- Returns: '' (empty string, no error)

-- Applied to a real query
SELECT SUBSTRING(name FROM 1 FOR GREATEST(0, LENGTH(name) - 10))
FROM employees;

Use a CASE expression for conditional handling:

SELECT
    name,
    CASE
        WHEN LENGTH(name) > 10
            THEN SUBSTRING(name FROM 1 FOR LENGTH(name) - 10)
        ELSE ''
    END AS trimmed_name
FROM employees;

Create a reusable safe wrapper function:

CREATE OR REPLACE FUNCTION safe_substring(
    p_string TEXT,
    p_start  INTEGER,
    p_length INTEGER
) RETURNS TEXT AS $$
BEGIN
    RETURN SUBSTRING(p_string FROM GREATEST(1, p_start) FOR GREATEST(0, p_length));
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Usage
SELECT safe_substring(product_name, 1, LENGTH(product_name) - 5)
FROM products;

---

Prevention Tips

• Always guard dynamic length expressions with GREATEST(0, expr) before passing them into SUBSTRING, OVERLAY, or any other string function that accepts a length argument. This single habit eliminates the majority of 22011 occurrences.

-- Defensive pattern template
SELECT SUBSTRING(col FROM start_pos FOR GREATEST(0, computed_length))
FROM your_table;

• Validate inputs at the application layer before they reach the database, and consider wrapping string manipulation logic inside plpgsql functions with explicit guard clauses. This keeps your SQL clean and makes edge-case handling explicit and testable.

---

Related Errors

Code	Name	Notes
22001	string_data_right_truncation	String result too long for target column
22003	numeric_value_out_of_range	Numeric overflow in expressions feeding into string functions
22027	trim_error	Similar class, occurs in TRIM operations

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.