PostgreSQL has a generate_subscripts() function that generates a series comprising the valid subscripts of a given array.

We pass the array to the function, along with the dimension that we want to use. We also have the option of returning the series in reverse order.

Example

Here’s a basic example to demonstrate:

SELECT generate_subscripts('{"Cat","Dog","Rabbit"}'::text[], 1);

Result:

 generate_subscripts 
---------------------
                   1
                   2
                   3
(3 rows)

So we can see that one row is returned for each array element, and the value of each row increments with each element.

Reverse Order

We can add TRUE as an argument to return the series in reverse order:

SELECT generate_subscripts(
    '{"Cat","Dog","Rabbit"}'::text[], 
    1, 
    TRUE
    ) AS "Reverse Order";

Result:

 Reverse Order 
---------------
             3
             2
             1
(3 rows)

Specify the Second Dimension

The second argument specifies the dimension. The previous example was a single-dimensional array and so I specified 1. For multi-dimensional arrays we can use 2 or 3 or whatever dimension we want to use.

Here’s an example of specifying the first dimension of a two-dimensional array:

SELECT generate_subscripts(
    '{ {"Cat","Dog"},{"Rabbit","Rat"},{"Bird","Pig"} }'::text[][], 
    1 
    ) AS "First Dimension";

Result:

 First Dimension 
-----------------
               1
               2
               3
(3 rows)

And here’s an example of specifying the second dimension:

SELECT generate_subscripts(
    '{ {"Cat","Dog"},{"Rabbit","Rat"},{"Bird","Pig"} }'::text[][], 
    2 
    ) AS "Second Dimension";

Result:

 Second Dimension 
------------------
                1
                2
(2 rows)

Extract the Array’s Elements

We can modify our query in order to extract the array’s elements at each corresponding subscript:

SELECT 
    s AS subscript, 
    a[s] AS value
FROM (
    SELECT generate_subscripts(a, 1) AS s, a 
    FROM (SELECT ARRAY['Cat','Dog','Rabbit','Horse'] AS a ));

Result:

 subscript | value  
-----------+--------
         1 | Cat
         2 | Dog
         3 | Rabbit
         4 | Horse
(4 rows)

And here it is in reverse:

SELECT 
    s AS subscript, 
    a[s] AS value
FROM (
    SELECT generate_subscripts(a, 1, TRUE) AS s, a 
    FROM (SELECT ARRAY['Cat','Dog','Rabbit','Horse'] AS a ));

Result:

 subscript | value  
-----------+--------
         4 | Horse
         3 | Rabbit
         2 | Dog
         1 | Cat
(4 rows)

Non-Existent Dimensions

If we specify a dimension that doesn’t exist in the array, zero rows are returned.

Example:

SELECT generate_subscripts('{"Cat","Dog","Rabbit"}'::text[], 2);

Result:

 generate_subscripts 
---------------------
(0 rows)