In PostgreSQL, the array_lower() function returns the lower bound of the specified array dimension. We pass the array and the relevant dimension, and it returns the lower bound for that dimension.

Example

Here’s a simple example to demonstrate:

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

Result:

1

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 so array_lower() returned 1.

We can also use array_dims() to get both the upper and lower bounds:

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

Result:

[1:5]

This function returns the subscript range.

We can use the generate_subscripts() function to output all subscripts for the array if required:

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_lower(ARRAY[[1,2,3,4], [5,6,7,8]], 1) AS "Dimension 1",
    array_lower(ARRAY[[1,2,3,4], [5,6,7,8]], 2) AS "Dimension 2";

Result:

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

That was a two dimensional array.

Here’s a three dimensional array:

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

Result:

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

Up until now, all arrays have been one based (which is the default), and so array_lower() has returned 1 in all cases.

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_lower() reflects the new lower bound for the subscript range.

Here’s an example of what I mean:

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

Result:

0

Here, we changed the array’s numbering so that it became a zero-based array. The array_lower() function returned 0 because of this.

Here’s another example:

SELECT array_lower('[-10:-8]={ "Cat", "Dog", "Bird" }'::text[], 1);

Result:

-10

In this case the numbering started at a negative number.

A Database Example

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

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

Result:

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

Here we can see that most arrays have an lower bound of 1, but one array is 10. In that array, the 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_lower(ARRAY[ 1,2,3 ], 2);

Result:

NULL