📋

Description

This Data Exception (error code 2202E) occurs when an attempt is made to access an element of an array using an index that is outside the valid range of the array's defined dimensions. It commonly arises in SQL queries or PL/pgSQL functions that manipulate array data types, indicating an attempt to read from or write to a non-existent position within the array.

💬

Error Message

array subscript error

🔍

Known Causes

4 known causes

⚠️

Array Index Out of Bounds

Attempting to access an array element using an index that is either too small (e.g., 0 for a 1-based array) or exceeds the array's defined upper limit.

⚠️

Accessing Empty or NULL Array

Trying to retrieve or modify an element from an array variable that has not been properly initialized, is empty, or is currently NULL, leading to an invalid subscript.

⚠️

Incorrect Multi-dimensional Indexing

Providing an invalid number of subscripts or incorrect index ranges when working with multi-dimensional arrays, causing an array subscript error.

⚠️

Dynamic Array Size Mismatch

Code logic incorrectly assumes a certain array size based on dynamic data, but the actual array dimensions at runtime are different, leading to an invalid subscript.

🛠️

Solutions

4 solutions available

1. Check Array Bounds Before Accessing easy

Validate array index is within the valid range before attempting access.

1

Before attempting to access an array element using an index, verify that the index is not out of bounds. PostgreSQL arrays are 1-based indexed by default, unless explicitly created with a different lower bound.

SELECT array_column[index_variable]
FROM your_table
WHERE index_variable >= 1 AND index_variable <= array_length(array_column, 1);

2

Alternatively, use a CASE statement or conditional logic within your query to handle cases where the index might be invalid. This prevents the error from occurring.

SELECT
    CASE
        WHEN index_variable >= 1 AND index_variable <= array_length(array_column, 1)
        THEN array_column[index_variable]
        ELSE NULL -- Or some default value
    END AS element_value
FROM your_table;

2. Handle NULL Array Columns Gracefully easy

Ensure your queries account for array columns that might be NULL.

1

If an array column itself is NULL, any attempt to access an element within it will result in an error. Use a COALESCE or CASE statement to handle NULL array columns.

SELECT
    CASE
        WHEN array_column IS NOT NULL AND index_variable >= 1 AND index_variable <= array_length(array_column, 1)
        THEN array_column[index_variable]
        ELSE NULL
    END AS element_value
FROM your_table;

3. Determine and Correct Array's Lower Bound medium

Understand and adapt to the actual lower bound of your array.

1

PostgreSQL arrays can have a lower bound other than 1. Inspect the array's definition or use the `unnest` function to understand its structure.

SELECT array_column FROM your_table LIMIT 1;

2

If the array's lower bound is not 1, adjust your index access accordingly. For example, if the lower bound is 0, you would access the first element with `array_column[0]`.

-- Example if lower bound is 0
SELECT array_column[0] FROM your_table;

3

You can use `unnest` to see the elements and their effective indices. This can help in debugging when the explicit lower bound isn't immediately obvious.

SELECT idx, val FROM unnest(array_column) WITH ORDINALITY AS t(val, idx) WHERE t.idx = your_desired_index;

4. Safely Unnest Arrays for Element Access medium

Use `unnest` to safely extract array elements, avoiding direct index errors.

1

The `unnest` function can be used to expand an array into a set of rows, making it easier to work with individual elements. This inherently handles boundary conditions.

SELECT t.id, elem
FROM your_table t, unnest(t.array_column) WITH ORDINALITY AS elem(elem, ordinal_pos)
WHERE ordinal_pos = your_desired_index;

2

If you need to access a specific element without necessarily knowing its exact index beforehand, or if you want to filter based on elements, `unnest` is a more robust approach.

SELECT t.id, elem
FROM your_table t, unnest(t.array_column) AS elem
WHERE elem = 'some_value';

🔗

Related Errors

5 related errors

PostgreSQL Error 2202E: Invalid Array Index Access