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.