By default, PostgreSQL arrays are one-based. This means that we need to use 1 if we want to reference the first element in the array, 2 for the second, and so on.

But we also have the option of specifying our own subscript range for an an array. For example we could create a zero-based array, a ten-based array, or even a negative value such as a negative ten-based array.

We can do this by using subscripted assignment to specify the actual subscript range for the array. Basically, we prefix the array with the subscript range, enclosed in square brackets, and an equals sign (=) between it and the array.

Example

Here’s a quick example to demonstrate:

SELECT ('[0:2]={ "Cat", "Dog", "Horse" }'::text[]);

Result:

[0:2]={Cat,Dog,Horse}

Here I created a zero-based array. The part that goes [0:2]= is the part that specifies the subscript range. We can see that the resulting array is created with the subscripted assignment.

Here it is with a different subscript range:

SELECT ('[10:12]={ "Cat", "Dog", "Horse" }'::text[]);

Result:

[10:12]={Cat,Dog,Horse}

And here it is starting at a negative value:

SELECT ('[-1:1]={ "Cat", "Dog", "Horse" }'::text[]);

Result:

[-1:1]={Cat,Dog,Horse}

Referencing Elements

We can reference elements in the array by using the subscripts that are applicable to those elements, based on our specified subscript range.

Example:

SELECT ('[10:12]={ "Cat", "Dog", "Horse" }'::text[])[11];

Result:

Dog

By way of another example, let’s take a database that contains arrays with various subscript ranges:

SELECT c1, c2 
FROM t1 
ORDER BY c1;

Result:

 c1 |         c2         
----+--------------------
  1 | {1,2,3,4,5}
  2 | [0:4]={1,2,3,4,5}
  3 | [-1:3]={1,2,3,4,5}
  4 | [4:8]={1,2,3,4,5}

Now let’s change the query so that it references a specific subscript:

\pset null 'null'
SELECT c1, c2[3]
FROM t1 
ORDER BY c1;

Result:

 c1 |  c2  
----+------
  1 |    3
  2 |    4
  3 |    5
  4 | null

Here I specified subscript 3. We can see that subscript 3 returns a different element, depending on the subscript range.

In my first line I simply specified that null values be output as null (in order to make the results more apparent). So we can see that the last row doesn’t actually have a subscript 3, and so null was returned.

Output the Subscripts

We can use the generate_subscripts() function to output the subscripts of our arrays:

SELECT generate_subscripts('[-1:3]={1,2,3,4,5}'::integer[], 1);

Result:

 generate_subscripts 
---------------------
                  -1
                   0
                   1
                   2
                   3