In PostgreSQL we can use the array_dims() function to return information about the dimensions in an array. The function accepts the array in question, and it returns a text representation of the array’s dimensions.

This function is not to be confused with the array_ndims() function, which simply returns the number of dimensions in an array.

Example

Here’s a quick example to demonstrate:

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

Result:

[1:5]

In this example, the array has five elements. The subscripts start at 1 and end at 5, and so array_dims() returns [1:5] accordingly.

Multi Dimensional Arrays

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

SELECT array_dims(ARRAY[[1,2,3,4], [5,6,7,8]]);

Result:

[1:2][1:4]

That was a two dimensional array.

Here’s a three dimensional array:

SELECT array_dims(ARRAY[[ARRAY[1,2,3,4]], [ARRAY[5,6,7,8]]]);

Result:

[1:2][1:1][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. But we can change the numbering system for any given array if we want. We can do this with subscripted assignment – where we prefix the array with the new subscript range.

Here’s an example of using array_dims() against a zero-based array:

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

Result:

[0:2]

We can see that the numbering starts at 0 and ends with 2. I defined this by prefixing [0:2]= to the array.

In this example I provided an array literal instead of using the ARRAY constructor like in the previous examples. Either way, we still ended up with an array.

Here’s another example:

SELECT array_dims('[0:2][-10:-8]={{45,78,13},{14,53,31},{98,26,27}}'::text[]);

Result:

[0:2][-10:-8]

This time we have a multi dimensional array. The outer array’s numbering is from 0 to 2. The inner array starts at a negative number (-10) and ends at another negative number (-8).

A Database Example

Suppose we have a table like this:

SELECT * FROM array_test;

Result:

      c1       |                     c2                      |                       c3                        
---------------+---------------------------------------------+-------------------------------------------------
 {1,2,3}       | {Rabbit,Cat,Dog,Monkey,Moth}                | {{1,2,3},{4,5,66},{7,8,9}}
 {41,52,67}    | {Zebra,Bird,Mouse,Cow,Pig,NULL,Swan}        | {{32,78,14},{102,99,37},{18,65,29}}
 [0:2]={8,7,9} | [-5:-1]={Gecko,Agra,Badger,Beaver,Platypus} | [1:3][10:12]={{45,78,13},{14,53,31},{98,26,27}}

Here’s an example of running array_dims() against all of those columns:

SELECT
    array_dims(c1),
    array_dims(c2),
    array_dims(c3)
FROM array_test;

Result:

 array_dims | array_dims |  array_dims  
------------+------------+--------------
 [1:3]      | [1:5]      | [1:3][1:3]
 [1:3]      | [1:7]      | [1:3][1:3]
 [0:2]      | [-5:-1]    | [1:3][10:12]