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) &lt;&gt; 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 documentation:


  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];    --&gt; 1
SELECT ('[-7:-5]={1,2,3}'::int[])[-6]; --&gt; 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