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

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?

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.

Related:

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

StackExchange Database Administrators Q#117670, answer score: 32