Why does PostgreSQL allow querying for array[0] even though it uses 1-based arrays?

Question

I have been playing with arrays in one of my PostgreSQL databases.

I have created a table with a geometry array with at least one element:

CREATE TABLE test_arrays (
    polygons geometry(Polygon,4326)[],
    CONSTRAINT non_empty_polygons_chk 
        CHECK ((COALESCE(array_length(polygons, 1), 0) <> 0))
);

I have added a few rows, and queried the table for the first element in each geometry array:

SELECT polygons[0] FROM test_arrays;

To my surprise, I got a list of empty rows!

After some research, it turns out that PostgreSQL arrays are one-based:

The array subscript numbers are written within square brackets. By default PostgreSQL uses a one-based numbering convention for arrays, that is, an array of n elements starts with array[1] and ends with array[n].

So SELECT polygons[0] FROM test_arrays; worked and returned the first polygon of each row.

If PostgreSQL uses a one-based numbering convention, why is querying the 0-th element allowed, and does the result have any meaning?

Read that page to the end: "Also, null is returned if a subscript is outside the array bounds (this case does not raise an error)" — , Oct 11 '15 at 09:54
@a_horse_with_no_name True and thank-you. I guess that the this design principle is intended to stop queries for raising errors and ending transactions due to index mismatches. — Adam Matan, Oct 11 '15 at 09:56

Erwin Brandstetter · Accepted Answer · 2021-10-26T19:29:16.880

Postgres arrays are 1-based by default. And in typical applications it's best to stick with the default. But the syntax allows to start with any integer number. The manual:

Subscripted assignment allows creation of arrays that do not use one-based subscripts. For example one might assign to myarray[-2:7] to create an array with subscript values from -2 to 7.

Examples:

SELECT ('[0:2]={1,2,3}'::int[])[0];    --> 1
SELECT ('[-7:-5]={1,2,3}'::int[])[-6]; --> 2

You can query any index of an array, if it does not exist you get NULL.

Normalize array subscripts for 1-dimensional array so they start with 1

Why does PostgreSQL allow querying for array[0] even though it uses 1-based arrays?

1 Answers1

Linked