Postgres allows us to create an array and perform several operations on it, such as adding new elements to it, removing unnecessary elements from it, etc. These operations can be performed using different built-in array functions and operators. One such function is the ARRAY_UPPER() which gives the upper bound/ the maximum value of the dimension of the array provided to it.

This guide will comprehensively illustrate the use of the ARRAY_UPPER() function in Postgres.

How to Use PostgreSQL ARRAY_UPPER() Function

The ARRAY_UPPER() function takes a couple of parameters and retrieves the upper bound of the dimension of the provided array. The basic query for the ARRAY_UPPER() function can be written as:

ARRAY_UPPER(array,dim)

● The ARRAY_UPPER() function takes in an array as a first argument.

● The second argument of this function is the dimension of the array along which we wish to find the upper bound.

Return Value

The ARRAY_UPPER() retrieves the upper bound of its dimensions or the highest index of the array in integer data type. It returns NULL if the dimension specified is greater than the original dimension of the specified array.

Let’s get into the examples to understand how the function actually works.

Example 1: Understanding the ARRAY_UPPER() Function

Now we will try to understand the workings of the ARRAY_UPPER() function by using the following query:

SELECT ARRAY_UPPER(ARRAY[10, 9, 8, 7], 1);

We have passed the array and the dimension in the ARRAY_UPPER() function. As a result, the query gives the highest/maximum index of the array dimension.

We can observe that the array is a one-dimensional array and the highest index of the specified array is 4. So the ARRAY_UPPER() function has returned 4.

We can also use the array_dims() function to get the dimensions of an array like this:

SELECT ARRAY_DIMS(ARRAY[10, 9, 8, 7])

This function returns the dimensions of the passed array like this:

We can see that the dimension of the array is [1:4] and the highest/ upper bound dimension of the array is 4. This function can be used to verify the simple cases.

Example 2: Using the ARRAY_UPPER() Function With 2-Dimensional Array

We can also implement the ARRAY_UPPER() function on the 2-dimensional array. Same as above, the array and the dimension along which we want to get the upper bound of the array (2 in this case), need to be specified as the function parameters. Consider the following query for this case:

SELECT ARRAY_UPPER(array[ [11,52,83],[14,55,76] ], 2);

We can see that the provided array is a 2-dimensional array which can be written as; [1:2][1:3] so the highest index in this query is 3. The query will return 3 as output.

We can witness that the query gave the same result as per our expectations.

Example 3: Specifying the Wrong Dimension in ARRAY_UPPER() Function

The dimension of the array must be specified correctly. If the dimension specified is invalid, this would return NULL. Let’s execute the same query with dimension 4.

SELECT ARRAY_UPPER(array[ ['Alex','Peter','Kate'],['Williams','Smith','Charles'] ], 4);

We can see that passing the incorrect dimension results in a NULL. we can also verify this by using the IS NULL Operator like this:

SELECT ARRAY_UPPER(
  array[ ['Alex','Peter','Kate'],['Williams','Smith','Charles'] ]
  , 4) IS NULL;

The IS NULL operator retrieves TRUE if the value is NULL otherwise it returns FALSE. So in this case the output is:

The query has yielded TRUE which simply ensures that the ARRAY_UPPER() function returns a NULL.

Note that the dimension specified needs to be smaller or equal to the original dimension of the provided array. If the dimension specified is greater(as in the above example), the query will return NULL.

Example 4: Using ARRAY_UPPER() Function With Multi-dimensional Array

We have seen a NULL case in the above example but the function only returns NULL if a greater dimension is specified than the original dimension of the array. Let's see this in the below query.

SELECT ARRAY_UPPER(
  array[ ['Alex','Peter','Kate'],['Williams','Smith','Charles'] ]
  , 1);

The provided array is 2-dimensional but we have specified 1 as the dimension along which we want to find the upper bound of the array dimension. In this case, the NULL value will not be returned by the ARRAY_UPPER() function. Instead, the array will be considered as a nested array with 2 elements(the inner arrays). So in the above case, when the array is acting as 1 dimensional, the array becomes the nested array with 2 elements i.e. “['Alex','Peter','Kate']” and “['Williams','Smith','Charles']”. Running the query will give the highest value of the array index. The query yields the following output.

The first array element, “['Alex','Peter','Kate']”, is at index 1, and “['Williams','Smith','Charles']” is at index 2. So the ARRAY_UPPER() function has returned 2.

Example 5: Advance Usage of ARRAY_UPPER() Function

We can also specify the starting and ending indexes of the array. Let’s consider the below query as an example to implement this.

SELECT ARRAY_UPPER('[0:3]={97,28,163,6}'::integer[], 1)

In the above query, we have specified the starting and ending indexes i.e. 0 and 3 respectively. This means that the indexing of the above array will start from 0 to 3. We can observe the array in string quotes so we have to do the typecasting into the integer data type. The second parameter illustrates that the array is a one-dimensional array.

Following is the output given by the query.

The highest index in this case is 3. That’s why the ARRAY_UPPER() function has returned 3.

Example 6: Using ARRAY_UPPER() Function With Multi-dimensional Array (Advanced)

The above given was a simple case when we had a one-dimensional array. The situation becomes tough and different in the case of 2 or higher-dimensional arrays. Consider the following case:

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

In the above query, the ending points are given which simply means that the query will assign the indexes according to the specified ending points. As in the above query, we want to get the upper bound of the array dimension along the first dimension. It means that the array will be considered as one-dimensional with the nested arrays which will be the elements of the array. This will automatically skip the second ending points i.e. [2:3]. Now, the indexing according to the [2:4] will take place i.e. 2, 3, 4, and the query will return the result accordingly. The output of this query should be 4, as the highest indexing value is 4.

The output of the above query is:

The result of the query is as per our expectations.

The scenario becomes somewhat different if we declare the dimension as 2 in the above query. Like this:

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

In this case, we specified the dimension as 2. This means that we want to find and get the dimension of the given array along the second dimension.

So the [2:3] will be considered the endpoints of the given nested arrays. We can see that the upper bound of the array dimensions in the above case of the second-dimensional array is 3. Let’s see the output of the query.

We can see that the query resulted in 3 as the upper bound of array dimensions. This is how the ARRAY_UPPER() function works.

Conclusion

The ARRAY_UPPER() function in PostgreSQL returns the upper bound of the array dimension or the highest index of an array. The ARRAY_UPPER() takes in the array and the dimension of an array along which to find the highest index as arguments and returns the highest index of the array. If the dimension specified in the function is greater than the dimension of the array, the function will give a NULL value as output. This post has illustrated the basic concept of the ARRAY_UPPER() function along with its working and examples.