In PostgreSQL, the array_upper() function returns the upper bound of the specified array dimension. We pass the array as well as the dimension, and it returns the upper bound accordingly.

Example

Here’s a simple example to demonstrate:

SELECT array_upper(ARRAY[ 'Zebra', 'Bird', 'Mouse', 'Cow', 'Pig' ], 1);

Result:

5

This array is a one dimensional array and so I specified 1 for the second argument.

The array is one based (PostgreSQL arrays are one based by default), and it contains five elements and so array_upper() returned 5.

We can verify that the array is one based by using array_lower():

SELECT array_lower(ARRAY[ 'Zebra', 'Bird', 'Mouse', 'Cow', 'Pig' ], 1);

Result:

1

Or by using array_dims():

SELECT array_dims(ARRAY[ 'Zebra', 'Bird', 'Mouse', 'Cow', 'Pig' ]);

Result:

[1:5]

This returns the subscript range, which we can see starts at 1.

Another way to check is with the generate_subscripts() function:

SELECT generate_subscripts(ARRAY[ 'Zebra', 'Bird', 'Mouse', 'Cow', 'Pig' ], 1);

Result:

 generate_subscripts 
---------------------
                   1
                   2
                   3
                   4
                   5

Multi Dimensional Arrays

Here’s an example that uses a multi dimensional array:

SELECT 
    array_upper(ARRAY[[1,2,3,4], [5,6,7,8]], 1) AS "Dimension 1",
    array_upper(ARRAY[[1,2,3,4], [5,6,7,8]], 2) AS "Dimension 2";

Result:

 Dimension 1 | Dimension 2 
-------------+-------------
           2 |           4

That was a two dimensional array.

Here’s a three dimensional array:

SELECT 
    array_upper(ARRAY[[ARRAY[1,2,3,4]], [ARRAY[5,6,7,8]]], 1) AS "Dimension 1",
    array_upper(ARRAY[[ARRAY[1,2,3,4]], [ARRAY[5,6,7,8]]], 2) AS "Dimension 2",
    array_upper(ARRAY[[ARRAY[1,2,3,4]], [ARRAY[5,6,7,8]]], 3) AS "Dimension 3";

Result:

 Dimension 1 | Dimension 2 | Dimension 3 
-------------+-------------+-------------
           2 |           1 |           4

Non One-Based Arrays

By default, PostgreSQL arrays are one-based. That is, the numbering starts at 1 and increments for each element in the array. We can change the numbering system for any given array by using subscripted assignment – where we prefix the array with the new subscript range.

When we do this, array_upper() reflects the new subscript range.

Here’s an example of what I mean:

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

Result:

2

Here, we changed the array’s numbering so that it became a zero-based array. The array contains three elements and so the upper bound is 2 (because the numbering goes 0, 1, 2).

A Database Example

Here’s an example of running array_upper() against a database column:

SELECT
    c3,
    array_upper(c3, 1) AS "Dimension 1",
    array_upper(c3, 2) AS "Dimension 2"
FROM array_test;

Result:

                       c3                        | Dimension 1 | Dimension 2 
-------------------------------------------------+-------------+-------------
 {{1,2,3},{4,5,66},{7,8,9}}                      |           3 |           3
 {{32,78,14},{102,99,37},{18,65,29}}             |           3 |           3
 [1:3][10:12]={{45,78,13},{14,53,31},{98,26,27}} |           3 |          12

Here we can see that most arrays have an upper bound of 3, but one array is 12. That’s because its subscripts range from 10 to 12.

Non-Existent Dimensions

Specifying a dimension that doesn’t exist in the array results in a NULL value being returned:

SELECT array_upper(ARRAY[ 1,2,3 ], 2);

Result:

NULL