By default, PostgreSQL arrays are one-based. That means that the numbering starts at subscript 1 and increments with each element in the array. However, we can change this so that the array starts at a different subscript.
Example
We can use subscripted assignment to create a zero-based array:
SELECT ('[0:2]={ "Cat", "Dog", "Horse" }'::text[]);Result:
[0:2]={Cat,Dog,Horse}
The bit at the front that goes [0:2]= is subscripted assignment that specifies the array’s range. In this case I specified that it goes from 0 to 2, which means that it’s a zero-based array.
Now when we want to reference an element, we need to use the subscript that adheres to the array’s zero-based range:
SELECT ('[0:2]={ "Cat", "Dog", "Horse" }'::text[])[1];Result:
Dog
In this case I appended [1] to the SELECT statement, which means that I wanted to return the element at subscript 1. Given this is a zero-based array, that subscript represents the second element in the array.