PostgreSQL raises an array subscript error when accessing an array element with an invalid or out-of-bounds index. This commonly occurs when using multi-dimensional arrays or when index values exceed the array bounds, especially in PL/pgSQL functions.
Fixes Array subscript error
-- WRONG: Accessing index 0
SELECT my_array[0];
-- CORRECT: Access index 1 for the first element
SELECT my_array[1];-- WRONG: Accessing index 0SELECT my_array[0];-- CORRECT: Access index 1 for the first elementSELECT my_array[1];Array subscript error
PostgreSQL arrays are 1-based by default, meaning the first element is at index 1, not 0. When you attempt to assign a value to an array element at an index outside the existing bounds, or access an index that does not exist, PostgreSQL raises an error. This is particularly important with multi-dimensional arrays, which cannot be dynamically resized like one-dimensional arrays.
Remember that PostgreSQL arrays are 1-based by default, not 0-based like many programming languages.
-- WRONG: Accessing index 0
SELECT my_array[0];
-- CORRECT: Access index 1 for the first element
SELECT my_array[1];Use the array_length() function to get the size of an array dimension, and validate indices before access:
-- Check the length of the first dimension
SELECT array_length(my_array, 1) AS array_size;
-- Safe access with bounds checking
SELECT
CASE
WHEN idx BETWEEN 1 AND array_length(my_array, 1)
THEN my_array[idx]
ELSE NULL
END AS safe_value
FROM table_name;For more explicit control, use array_upper() and array_lower() to get the actual upper and lower bounds:
SELECT
array_lower(my_array, 1) AS lower_bound,
array_upper(my_array, 1) AS upper_bound;
-- Safe loop in PL/pgSQL
FOR i IN array_lower(my_array, 1) .. array_upper(my_array, 1) LOOP
RAISE NOTICE 'Element: %', my_array[i];
END LOOP;For multi-dimensional arrays, use array_fill() to create properly sized arrays before assignment:
-- Create a 3x2 array filled with NULL
DECLARE
my_array INT[][];
BEGIN
my_array := array_fill(NULL::INT, ARRAY[3, 2]);
-- Now safe to assign to any position within [1:3][1:2]
my_array[2][1] := 42;
END;Add explicit bounds checking in PL/pgSQL to prevent errors:
CREATE FUNCTION safe_array_access(arr INT[], idx INT) RETURNS INT AS $$
BEGIN
IF idx < array_lower(arr, 1) OR idx > array_upper(arr, 1) THEN
RETURN NULL;
END IF;
RETURN arr[idx];
END;
$$ LANGUAGE plpgsql IMMUTABLE;PostgreSQL differs from many languages in its 1-based array indexing and its handling of out-of-bounds reads (which return NULL rather than error). When assigning to arrays, you can expand 1-dimensional arrays by assigning to an index beyond the current size, filling intermediate positions with NULL. However, multi-dimensional arrays have stricter rules—you cannot dynamically resize them by assigning to arbitrary indices. Always initialize multi-dimensional arrays with array_fill() or literal syntax to define all dimensions upfront. In historical PostgreSQL versions, there were security fixes related to integer overflow in array subscript calculations, so ensure you are running a patched version if working with very large arrays.