Why does PostgreSQL allow querying for array[0] even though it uses 1-based arrays?
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