In PostgreSQL the array_ndims() function returns the number of dimensions of an array. We pass the array to the function, and it returns an integer of the number of dimensions in the array.

This function is not to be confused with the array_dims() function, which returns a more detailed text representation of the dimensions in the array.

Example

Here’s a quick example to demonstrate the array_ndims() function:

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

Result:

1

This array is a one dimensional array and so array_ndims() returns 1.

Multi Dimensional Arrays

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

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

Result:

2

That was a two dimensional array.

Here’s a three dimensional array:

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

Result:

3

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.

However, this doesn’t change the number of dimensions in the array. If the array is one dimensional then we will still get 1 when we run it through array_ndims(), regardless of any change in the array’s numbering.

Here’s an example of what I mean:

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

Result:

1

Here, we changed the array’s numbering so that it became a zero-based array. Regardless, the array_dims() function simply returned the number of dimensions in the array.

The array_dims() function’s result on the other hand, would reflect the new subscript range.

A Database Example

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

SELECT
    c3,
    array_ndims(c3)
FROM array_test;

Result:

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

All columns contain a two dimensional array, and so array_dims() returns 2 for all of them.