📋

Description

Error 22011, a `data_exception` known as `substring_error`, occurs when an invalid argument is provided to a string substring function in PostgreSQL. This typically signifies that the requested starting position or length for the substring operation is out of bounds or otherwise malformed.

💬

Error Message

substring error

🔍

Known Causes

3 known causes

⚠️

Invalid Start Position

The starting position specified for the substring operation is zero, negative, or exceeds the total length of the string being processed.

⚠️

Invalid Length Parameter

The requested length for the substring is negative, or specifying it would cause the substring to extend beyond the string's end from the given start position.

⚠️

Incorrect Data Type

Attempting to apply a substring function to a column or expression that is not a string type (e.g., numeric or boolean), leading to a type mismatch.

🛠️

Solutions

3 solutions available

1. Correct Substring Length and Offset easy

Ensure the length and offset parameters in your SUBSTRING function are valid and within the bounds of the string.

1

Review the `SUBSTRING` function call in your SQL query. The function signature is `SUBSTRING(string FROM start FOR length)` or `SUBSTRING(string, start, length)`. The `start` parameter is 1-based.

2

Verify that the `start` parameter is not greater than the length of the string. If it is, the result will be an empty string, but for certain operations or if combined with other functions, it might lead to this error.

3

Ensure that the `length` parameter, when added to the `start` parameter, does not exceed the total length of the string in a way that causes an invalid operation. While PostgreSQL is generally lenient, extremely large or negative lengths can cause issues.

4

If you are dynamically generating the `start` or `length` values, add checks to ensure they are within valid ranges before passing them to `SUBSTRING`.

-- Example: Check for valid start and length
SELECT 
    CASE 
        WHEN LENGTH(your_string_column) < start_pos THEN '' -- Return empty string if start is beyond string length
        ELSE SUBSTRING(your_string_column FROM start_pos FOR substring_length)
    END AS substring_result
FROM 
    your_table;

2. Handle NULL or Empty Strings Gracefully easy

Add checks for NULL or empty strings before attempting to use the SUBSTRING function.

1

The `SUBSTRING` function can behave unexpectedly or error out when applied to NULL or empty strings, especially if combined with other operations. Use `COALESCE` or `CASE` statements to provide default values or skip the operation.

-- Using COALESCE to provide an empty string if the input is NULL
SELECT 
    SUBSTRING(COALESCE(your_string_column, '') FROM start_pos FOR substring_length)
FROM 
    your_table;

2

Alternatively, use a `CASE` statement to conditionally apply `SUBSTRING` only when the string is not NULL and has a sufficient length.

SELECT 
    CASE 
        WHEN your_string_column IS NULL OR LENGTH(your_string_column) < start_pos THEN ''
        ELSE SUBSTRING(your_string_column FROM start_pos FOR substring_length)
    END AS substring_result
FROM 
    your_table;

3. Inspect Data for Unexpected Characters or Encoding Issues medium

Investigate the actual data in your table for any non-standard characters or encoding problems that might be confusing the substring operation.

1

Run a query to fetch the data that is causing the error. Pay close attention to the column where `SUBSTRING` is being applied.

SELECT your_string_column FROM your_table WHERE ... -- Add conditions to isolate the problematic row(s)

2

Use functions like `LENGTH()`, `OCTET_LENGTH()`, and `encode()` to inspect the byte representation of the string. This can reveal if there are multi-byte characters or unexpected byte sequences that are not being handled correctly.

SELECT 
    your_string_column,
    LENGTH(your_string_column) AS char_length,
    OCTET_LENGTH(your_string_column) AS byte_length,
    encode(your_string_column::bytea, 'hex') AS hex_representation
FROM 
    your_table 
WHERE ...

3

If you find problematic characters, you might need to clean the data. This could involve using functions like `translate()`, `regexp_replace()`, or `convert()` to normalize the encoding or remove specific characters.

-- Example: Removing non-ASCII characters (adjust as needed)
UPDATE your_table 
SET your_string_column = regexp_replace(your_string_column, '[^\x00-\x7F]+', '', 'g') 
WHERE ...

🔗

Related Errors

5 related errors

PostgreSQL Error 22011: Invalid Substring Operation