PostgreSQL 22011 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 22011: Substring Error

PostgreSQL error code 22011 (substring_error) is raised when the SUBSTRING() function or related string manipulation functions receive invalid argument values, most commonly a negative length parameter. This error strictly enforces SQL standard rules that require the length argument to be a non-negative integer. It frequently surfaces in production when user-supplied input or dynamically computed values are passed directly into string functions without validation.

Top 3 Causes

1. Negative Length Argument in SUBSTRING()

Passing a negative value as the length parameter is the most common cause of this error.

-- This will raise ERROR 22011
SELECT SUBSTRING('Hello PostgreSQL' FROM 1 FOR -5);
-- ERROR:  negative substring length not allowed

-- Fix: Use GREATEST() to clamp the minimum to 0
SELECT SUBSTRING('Hello PostgreSQL' FROM 1 FOR GREATEST(0, -5));
-- Returns: '' (empty string, no error)

-- Fix: Use a CASE guard before calling SUBSTRING
SELECT
    CASE
        WHEN computed_length >= 0
            THEN SUBSTRING(col FROM start_pos FOR computed_length)
        ELSE ''
    END AS result
FROM your_table;

2. Dynamically Computed Length Goes Negative

When you calculate a length from two position values (e.g., end_pos - start_pos), the result can unexpectedly become negative if the data doesn't match your assumptions.

-- Dangerous: no guard on the computed length
SELECT
    SUBSTRING(raw_col FROM start_idx FOR end_idx - start_idx)
FROM source_table;
-- Fails when end_idx < start_idx

-- Safe version using GREATEST and NULLIF
SELECT
    NULLIF(
        SUBSTRING(
            raw_col
            FROM GREATEST(1, start_idx)
            FOR GREATEST(0, end_idx - start_idx)
        ),
        ''
    ) AS extracted
FROM source_table;

-- Better yet, use SPLIT_PART() when splitting on a delimiter
SELECT SPLIT_PART(email_col, '@', 2) AS domain
FROM users;
-- Returns '' safely when '@' is absent

3. Invalid Arguments in OVERLAY()

The OVERLAY() function shares the same error code when given an invalid FOR clause value.

-- Raises 22011
SELECT OVERLAY('Hello World' PLACING 'PG' FROM 1 FOR -2);

-- Correct usage
SELECT OVERLAY('Hello World' PLACING 'PG' FROM 1 FOR 2);
-- Returns: 'PGllo World'

-- Defensive wrapper function
CREATE OR REPLACE FUNCTION safe_overlay(
    p_source    TEXT,
    p_new       TEXT,
    p_start     INT,
    p_len       INT DEFAULT NULL
) RETURNS TEXT AS $$
DECLARE
    v_len INT := COALESCE(p_len, LENGTH(p_new));
BEGIN
    IF p_start < 1 OR v_len < 0 THEN
        RAISE WARNING 'Invalid OVERLAY args: start=%, len=%', p_start, v_len;
        RETURN p_source;
    END IF;
    RETURN OVERLAY(p_source PLACING p_new FROM p_start FOR v_len);
END;
$$ LANGUAGE plpgsql;

Quick Fix Solutions

-- Universal safe substring wrapper
CREATE OR REPLACE FUNCTION safe_substr(
    p_text  TEXT,
    p_start INT,
    p_len   INT
) RETURNS TEXT AS $$
BEGIN
    RETURN SUBSTRING(
        p_text
        FROM GREATEST(1, p_start)
        FOR GREATEST(0, p_len)
    );
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

-- Usage
SELECT safe_substr('PostgreSQL', 1, -3);  -- Returns ''
SELECT safe_substr('PostgreSQL', 1, 6);   -- Returns 'Postgr'

Prevention Tips

Always sanitize dynamic length values. Wrap every computed length with GREATEST(0, computed_length) before passing it to SUBSTRING() or OVERLAY(). Centralise string extraction logic in reusable PL/pgSQL functions so validation happens in one place.
Prefer higher-level string functions when possible. Functions like SPLIT_PART(), LEFT(), RIGHT(), and REGEXP_SUBSTR() handle edge cases more gracefully than manual SUBSTRING() arithmetic and rarely trigger 22011.

-- Prefer these over manual SUBSTRING arithmetic
SELECT LEFT('PostgreSQL', 6);           -- 'Postgr'
SELECT RIGHT('PostgreSQL', 3);          -- 'SQL'
SELECT SPLIT_PART('a,b,c', ',', 2);    -- 'b'

Related Errors

Code	Name	Relation
22001	string_data_right_truncation	String too long for target column
22003	numeric_value_out_of_range	Often follows 22011 when casting the result
22007	invalid_datetime_format	Chained error after bad SUBSTRING + CAST
42883	undefined_function	Wrong argument types passed to SUBSTRING

📖 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.

DEV Community