PostgreSQL Array Subscript Error (SQLSTATE 2202E)

PostgreSQL raises ERROR: array subscript out of range (SQLSTATE 2202E, condition name array_subscript_error) when an array subscript or slice boundary is outside the valid range for the array's declared dimensions, or when a subscript value itself is structurally invalid (e.g., a non-integer expression used in a context that requires an integer index). This belongs to error class 22 (Data Exception) in the SQL standard.

What This Error Means

SQLSTATE class 22 covers data exceptions — situations where the data itself violates a constraint or rule at the value level, not at the schema or permissions level. 2202E is the specific code for array subscript problems, a subtype of the broader array-handling errors in that class (which also includes 2202D for null array subscript).

PostgreSQL arrays are 1-based by default and can be declared with explicit lower and upper bounds. When you write my_array[n] or a slice like my_array[2:5], PostgreSQL validates that the subscript expression produces a value consistent with those bounds. If the array was declared with a specific dimension (e.g., integer[3]) and you access beyond it, or if the index expression resolves to a value that is structurally out of range for that array's storage, 2202E is raised.

After this error is raised, the current transaction is placed into an aborted state. Any subsequent statements in the same transaction block will fail with ERROR: current transaction is aborted, commands ignored until end of transaction block until you issue a ROLLBACK (or ROLLBACK TO SAVEPOINT). The error does not terminate the connection itself.

Note: PostgreSQL normally does not raise an error when you read beyond the bounds of an unconstrained array — it simply returns NULL. 2202E is more likely to appear during write operations, in expressions involving fixed-dimension arrays, or when working with array-returning functions that enforce strict bound checks.

Common Causes

1. Writing to a subscript outside the declared dimension of a fixed-size array column. If a column is defined as integer[3] (three elements exactly) and you attempt to assign a fourth element, PostgreSQL will reject the value with 2202E.

2. Using a non-integer or NULL subscript expression. An expression like arr[some_float] where some_float is 1.9 may be cast implicitly, but a NULL subscript produces a 2202D (null array subscript), and certain invalid casts can produce 2202E.

3. Slice bounds that are inverted or out of range in strict contexts. Functions or operators that enforce strict bound semantics (e.g., certain intarray extension operations, or PL/pgSQL array manipulation in loops) can raise 2202E when upper < lower in a slice.

4. Array input from external sources with mismatched dimensions. When receiving array literals via JDBC, libpq, or application ORMs, a mismatch between the declared column type and the provided array structure can trigger subscript validation errors during assignment.

5. PL/pgSQL code that iterates with an off-by-one index. Writing a loop that calculates an index based on array_length() or cardinality() but then applies an offset can easily go out of range when the array is empty or shorter than expected.

How to Fix array_subscript_error

1. Check the actual bounds of the array before accessing it.

   -- Get lower and upper bounds for dimension 1
   SELECT array_lower(my_array, 1), array_upper(my_array, 1)
   FROM my_table
   WHERE id = 42;
   
   -- Only access if the index is within bounds
   SELECT CASE
     WHEN array_length(my_array, 1) >= 3 THEN my_array[3]
     ELSE NULL
   END
   FROM my_table;
   

2. Use cardinality() or array_length() guards in PL/pgSQL loops.

   DO $$
   DECLARE
     arr integer[] := ARRAY[10, 20, 30];
     i   integer;
   BEGIN
     FOR i IN 1 .. cardinality(arr) LOOP
       RAISE NOTICE 'Element %: %', i, arr[i];
     END LOOP;
   END;
   $$;
   

   Avoid hardcoding upper bounds that may not match the actual array length at runtime.

3. Avoid fixed-dimension array column types unless strictly required.

   Declaring a column as integer[3] in PostgreSQL is syntactically accepted but the dimension constraint is not enforced by standard PostgreSQL — it behaves like integer[]. However, custom domains and some extensions do enforce dimension constraints. If you are hitting 2202E on writes, verify whether a domain or extension is adding that constraint, and either widen the type or validate input before insert.

4. Validate array input from application code before sending to PostgreSQL.

   -- Check that the array is not empty before slicing
   SELECT my_array[2:4]
   FROM my_table
   WHERE my_array IS NOT NULL
     AND array_length(my_array, 1) >= 2;
   

5. Handle the error in PL/pgSQL using an exception handler.

   DO $$
   DECLARE
     arr integer[] := ARRAY[1, 2];
   BEGIN
     RAISE NOTICE '%', arr[5];
   EXCEPTION
     WHEN array_subscript_error THEN
       RAISE NOTICE 'Subscript out of range, skipping.';
   END;
   $$;
   

   The condition name array_subscript_error can be used directly in EXCEPTION WHEN clauses.

Additional Information

• SQLSTATE 2202E belongs to class 22 (Data Exception). Related codes in the same class include 2202D (null_array_subscript) and 2202G (invalid_tablesample_argument), as well as the general 2200 (data exception) family.
• The condition name array_subscript_error is available by name in PL/pgSQL EXCEPTION WHEN blocks since at least PostgreSQL 9.x and is defined in errcodes.txt in the PostgreSQL source.
• Most application drivers (JDBC, psycopg2, asyncpg, libpq) surface this as a DataError or equivalent, with the SQLSTATE 2202E available in the exception's pgcode attribute (Python) or getSQLState() method (Java).
• In practice, standard unconstrained PostgreSQL arrays (integer[]) return NULL rather than raising 2202E for out-of-bounds reads. The error is more common when using extensions such as intarray, domain-constrained array types, or PL/pgSQL code that performs index arithmetic.
• There are no significant performance implications beyond the transaction abort overhead. The fix is always at the application or query logic level.

Frequently Asked Questions

Why does reading arr[100] return NULL instead of an error, but I'm still seeing 2202E?

For unconstrained array types (integer[], text[], etc.), PostgreSQL silently returns NULL for out-of-bounds read subscripts rather than raising an error. 2202E is most commonly raised during write/update operations, when using fixed-dimension domain types, or when an extension (such as intarray) imposes stricter bounds checking. Review whether the offending statement is a SELECT, UPDATE, or a call into an extension function.

Can I catch 2202E in application code without a PL/pgSQL wrapper?

Yes. Any PostgreSQL driver that exposes the SQLSTATE from server errors lets you catch it. In Python with psycopg2 or psycopg3, the exception will be a DataError and e.pgcode will equal '2202E'. In Java with JDBC, call e.getSQLState() on the SQLException. You can then decide whether to retry with a corrected subscript or log and skip the offending row.

Does PostgreSQL enforce array dimension declarations like integer[3]?

No — in standard PostgreSQL, the number inside brackets in a column type declaration (integer[3]) is stored in the catalog but is not enforced at runtime. The column will accept arrays of any length. If you are seeing 2202E on writes to such a column, a domain, a trigger, or an extension is likely adding enforcement. Query \d+ tablename in psql and check for domain types or triggers that might validate the array.

What is the difference between SQLSTATE 2202D and 2202E?

2202D (null_array_subscript) is raised specifically when the subscript expression itself evaluates to NULL — for example, arr[NULL] in a context where a null subscript is not permitted. 2202E (array_subscript_error) covers other invalid subscript situations, such as an out-of-range integer index in a strict context. Both are subclasses of the class 22 data exception family.