DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 2202E Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 2202E: Array Subscript Error

PostgreSQL error code 2202E — array_subscript_error — is raised when an invalid subscript (index) is used to access an array element. This typically happens when the index is NULL, not an integer, or falls outside acceptable bounds during array slicing. If you're working with arrays dynamically in queries or PL/pgSQL functions, this error can surface unexpectedly in production.

Top 3 Causes

1. Using NULL as an Array Index

Passing a NULL value as an array subscript is the most common cause. PostgreSQL cannot resolve a NULL index and throws the error immediately.

-- Triggers 2202E
DO $$
DECLARE
    arr integer[] := ARRAY[10, 20, 30];
    idx integer;   -- NULL by default
BEGIN
    RAISE NOTICE '%', arr[idx];  -- ERROR: array subscript error
END;
$$;

-- Fix: use COALESCE to provide a safe default
DO $$
DECLARE
    arr integer[] := ARRAY[10, 20, 30];
    idx integer;
BEGIN
    RAISE NOTICE '%', arr[COALESCE(idx, 1)];  -- Returns 10 safely
END;
$$;
Enter fullscreen mode Exit fullscreen mode

2. Non-Integer Subscript Value

Array indices must be integers. Passing a float, text, or uncast value causes this error.

-- Potential issue: float used as index
SELECT (ARRAY['a','b','c'])[2.7];  -- May raise 2202E

-- Fix: explicitly cast to integer
SELECT (ARRAY['a','b','c'])[FLOOR(2.7)::integer];  -- Returns 'b'

-- Fix: validate and cast user-supplied index
CREATE OR REPLACE FUNCTION safe_get(arr text[], raw_idx text)
RETURNS text AS $$
DECLARE
    idx integer;
BEGIN
    idx := raw_idx::integer;  -- Will raise error if not numeric
    IF idx < 1 OR idx > array_length(arr, 1) THEN
        RETURN NULL;
    END IF;
    RETURN arr[idx];
END;
$$ LANGUAGE plpgsql;
Enter fullscreen mode Exit fullscreen mode

3. Invalid Range in Array Slicing

When using slice syntax array[s:e], if s or e is NULL or the range is logically invalid, PostgreSQL raises this error.

-- Problematic slice with NULL bounds
DO $$
DECLARE
    arr    integer[] := ARRAY[1,2,3,4,5];
    s_idx  integer;   -- NULL
BEGIN
    RAISE NOTICE '%', arr[s_idx:3];  -- 2202E triggered
END;
$$;

-- Fix: normalize slice bounds before use
SELECT scores[
    GREATEST(1, COALESCE(start_pos, 1)) :
    LEAST(array_length(scores, 1), COALESCE(end_pos, array_length(scores, 1)))
] AS safe_slice
FROM student_records
WHERE scores IS NOT NULL;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  • Always check for NULL before using a variable as an array index — use COALESCE or an IF guard.
  • Cast explicitly using ::integer when index values come from user input or computed expressions.
  • Validate range using array_length(arr, 1) to ensure the index is within [1, length].
  • Use a wrapper function that returns a default value instead of raising an error: 
CREATE OR REPLACE FUNCTION safe_array_get(
    p_array anyarray,
    p_index integer,
    p_default anyelement DEFAULT NULL
) RETURNS anyelement AS $$
BEGIN
    IF p_array IS NULL OR p_index IS NULL THEN RETURN p_default; END IF;
    IF p_index < 1 OR p_index > array_length(p_array, 1) THEN RETURN p_default; END IF;
    RETURN p_array[p_index];
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Usage
SELECT safe_array_get(ARRAY[1,2,3], 5, -1);  -- Returns -1, no error
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Validate at the application layer — before sending index values to PostgreSQL, ensure they are non-NULL integers within a known safe range. Use prepared statements to enforce type checking.

  2. Centralize array access in a utility function — wrap all array element access through a single validated function (like safe_array_get above). This makes your codebase resilient and debugging much easier when array-related errors occur.

Related Errors

Code Name Relation
22P02 invalid_text_representation Raised when casting a non-numeric string to integer for use as index
22003 numeric_value_out_of_range Index value exceeds integer bounds
2202D invalid_row_count_in_result_offset_clause Related range-validation category

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

Top comments (0)