How to fix 22011: substring_error in PostgreSQL

PostgreSQLBEGINNERLOW

This PostgreSQL error occurs when invalid arguments are passed to the SUBSTRING() function, which extracts parts of strings. The error typically happens when start positions or lengths are out of bounds, negative, or incompatible with the input string data type.

What this error means

The SUBSTRING() function in PostgreSQL extracts a substring from a string based on starting position and optional length. The error "22011: substring_error" occurs when the arguments violate constraints for substring extraction. This is a data exception error (SQLSTATE class 22) that indicates invalid parameters for string manipulation. The SUBSTRING() function has several forms: 1. SUBSTRING(string FROM start FOR length) 2. SUBSTRING(string FROM start) 3. SUBSTRING(string, start, length) 4. SUBSTRING(string, start) The error typically happens when: 1. Start position is zero or negative (unless using special PostgreSQL extensions) 2. Length is negative 3. Start position exceeds string length (for certain argument forms) 4. Invalid combination of arguments for the specific SUBSTRING() syntax being used PostgreSQL's SUBSTRING() follows SQL standard rules where string positions start at 1, not 0. However, PostgreSQL also supports negative start positions to count from the end of the string.

How to fix "22011: substring_error"

1Check your SUBSTRING() arguments

Examine the SUBSTRING() function call in your query. Common syntax forms:

sql

-- SQL standard syntax
SUBSTRING(string FROM start FOR length)
SUBSTRING(string FROM start)

-- PostgreSQL traditional syntax
SUBSTRING(string, start, length)
SUBSTRING(string, start)

Verify that:
1. Start position is valid (≥ 1 for forward counting, or ≤ -1 for backward counting)
2. Length is positive (if specified)
3. String is not NULL unless handled
4. Data types are compatible

Example of problematic calls:

sql

-- This will fail: start position 0
SELECT SUBSTRING('hello' FROM 0 FOR 3);

-- This will fail: negative length
SELECT SUBSTRING('hello', 2, -1);

-- This might fail: start exceeds length (depends on syntax)
SELECT SUBSTRING('hello' FROM 10);

2Understand PostgreSQL's SUBSTRING() behavior

PostgreSQL's SUBSTRING() has specific rules:

1. Start positions:
- Positive numbers count from the beginning (1 = first character)
- Negative numbers count from the end (-1 = last character)
- Zero is invalid in standard SQL mode

2. Length:
- Must be positive integer
- If omitted, extracts to end of string
- If length would exceed string, extracts available characters

3. Edge cases:
- Empty string returns empty string
- NULL string returns NULL
- Start position beyond string length returns empty string (in some forms)

Test with simple values:

sql

-- Valid examples
SELECT SUBSTRING('PostgreSQL' FROM 1 FOR 4);  -- 'Post'
SELECT SUBSTRING('PostgreSQL', 6);           -- 'SQL'
SELECT SUBSTRING('PostgreSQL' FROM -3 FOR 3); -- 'SQL' (last 3 chars)

-- Check your actual values
SELECT
  your_string,
  LENGTH(your_string) as str_len,
  your_start_position,
  your_length
FROM your_table
WHERE your_conditions;

3Add validation for dynamic arguments

When start positions or lengths are calculated dynamically, add validation:

sql

-- Use CASE to handle invalid positions
SELECT
  CASE
    WHEN start_pos <= 0 THEN NULL  -- Handle invalid start
    WHEN substr_len <= 0 THEN NULL -- Handle invalid length
    ELSE SUBSTRING(your_string, start_pos, substr_len)
  END as safe_substring
FROM your_table;

-- Or use GREATEST/LEAST to bound values
SELECT SUBSTRING(
  your_string,
  GREATEST(1, start_pos),  -- Ensure at least 1
  LEAST(LENGTH(your_string), substr_len)  -- Don't exceed string length
)
FROM your_table;

-- For negative start positions (counting from end)
SELECT SUBSTRING(
  your_string,
  CASE
    WHEN start_pos < 0 THEN LENGTH(your_string) + start_pos + 1
    ELSE start_pos
  END,
  substr_len
)
FROM your_table
WHERE start_pos <> 0;  -- Exclude zero positions

4Handle NULL values and edge cases

SUBSTRING() returns NULL if the input string is NULL. Add NULL handling:

sql

-- Use COALESCE to provide defaults
SELECT SUBSTRING(
  COALESCE(your_string, ''),
  COALESCE(start_pos, 1),
  COALESCE(substr_len, LENGTH(COALESCE(your_string, '')))
)
FROM your_table;

-- Or filter out NULL rows
SELECT SUBSTRING(your_string, start_pos, substr_len)
FROM your_table
WHERE your_string IS NOT NULL
  AND start_pos IS NOT NULL
  AND substr_len IS NOT NULL;

-- Handle empty strings specially
SELECT
  CASE
    WHEN your_string = '' THEN ''
    WHEN start_pos <= 0 THEN NULL
    WHEN substr_len <= 0 THEN NULL
    ELSE SUBSTRING(your_string, start_pos, substr_len)
  END as result
FROM your_table;

5Consider alternative string functions

If SUBSTRING() is causing persistent issues, consider alternatives:

1. Use LEFT() and RIGHT() for simple cases:

sql

-- Instead of SUBSTRING(string FROM 1 FOR n)
SELECT LEFT(your_string, n) FROM your_table;

-- Instead of SUBSTRING(string FROM -n)
SELECT RIGHT(your_string, n) FROM your_table;

2. Use split_part() for delimiter-based extraction:

sql

-- Extract domain from email
SELECT split_part(email, '@', 2) as domain
FROM users;

3. Use regexp_replace() or regexp_match() for pattern extraction:

sql

-- Extract numbers from string
SELECT (regexp_match(your_string, 'd+'))[1] as first_number
FROM your_table;

4. Use overlay() for replacement within substrings:

sql

-- Replace part of string
SELECT OVERLAY('PostgreSQL' PLACING 'SQL' FROM 6) as result;  -- 'PostSQL'

5. Use substr() alias (same as SUBSTRING but different error handling):

sql

-- substr() is a PostgreSQL alias for SUBSTRING
SELECT substr(your_string, start_pos, substr_len)
FROM your_table;

Advanced notes

### PostgreSQL SUBSTRING() Implementation Details

1. Standard vs. PostgreSQL Behavior:
- SQL standard: Positions start at 1, zero is invalid
- PostgreSQL extension: Negative positions count from end
- Some other databases use 0-based indexing (check compatibility)

2. Performance Considerations:
- SUBSTRING() can't use indexes on the result
- For frequent substring operations, consider computed columns
- Large strings with many SUBSTRING() calls can be slow

3. Character Set and Encoding Issues:
- SUBSTRING() works with character positions, not bytes
- For multibyte encodings (UTF-8), position refers to characters
- Use octet_length() for byte-based operations

4. Alternative Syntax Forms:

sql

-- All these are equivalent in PostgreSQL
SELECT SUBSTRING('hello' FROM 2 FOR 3);
SELECT SUBSTRING('hello', 2, 3);
SELECT substr('hello', 2, 3);
SELECT 'hello'[2:4];  -- Array slice syntax for strings

5. Common Use Patterns:

sql

-- Extract first word
SELECT SUBSTRING(description FROM 1 FOR POSITION(' ' IN description) - 1)
FROM products
WHERE description LIKE '% %';

-- Extract file extension
SELECT SUBSTRING(filename FROM '.([^.]+)$')
FROM documents;

-- Mask sensitive data
SELECT
  name,
  SUBSTRING(email FROM 1 FOR 3) || '***' || SUBSTRING(email FROM POSITION('@' IN email))
FROM users;

### Error Code Hierarchy

- SQLSTATE Class: 22 (Data Exception)
- Subclass: 011 (Substring Error)
- Full code: 22011

This error is raised by PostgreSQL's string processing functions when substring parameters violate constraints. The exact behavior may vary slightly between PostgreSQL versions.

### Debugging Tips

1. Use pg_typeof() to check data types:

sql

SELECT pg_typeof(your_column), your_column
FROM your_table
LIMIT 1;

2. Test with literal values first:

sql

-- Isolate the problematic substring call
SELECT SUBSTRING('test string', 1, 4);  -- Should work

3. Check for hidden characters:

sql

-- Non-printing characters can affect length calculations
SELECT your_string, LENGTH(your_string), octet_length(your_string)
FROM your_table
WHERE your_string LIKE '%' || CHR(9) || '%';  -- Tab character

How to fix 22011: substring_error in PostgreSQL

What this error means

Typical symptoms

Common causes

How to fix "22011: substring_error"

Advanced notes

Related errors

Official resources & further reading