In PostgreSQL, the array_length() function returns the length of the specified array dimension.

We pass the array as the first argument, and the dimension as the second.

Example

Here’s a basic example to demonstrate:

SELECT array_length(array[ 'Ben', 'Jen', 'Len' ], 1);

Result:

3

Here I passed an array with three elements and so the result is 3.

My second argument was 1, because I wanted the length of the first dimension of the array (which in this case happens to be the only dimension).

Specifying a Non-Existent Dimension

If the second argument references a dimension that doesn’t exist, then null is returned:

SELECT array_length(array[ 'Ben', 'Jen', 'Len' ], 2);

Result:

null

In this case I specified 2 (for the second dimension) but the array only has one dimension and so null was returned.

Multi Dimensional Arrays

With multi dimensional arrays, the length will depend on which dimension we specify (and the length of the array at that dimension).

Here’s a database example:

SELECT 
    c1,
    c3,
    array_length(c3, 1) AS "1st Dimension",
    array_length(c3, 2) AS "2nd Dimension",
    array_length(c3, 3) AS "3rd Dimension"
FROM t1
ORDER BY c1;

Result:

 c1 |               c3                | 1st Dimension | 2nd Dimension | 3rd Dimension 
----+---------------------------------+---------------+---------------+---------------
  1 | {{1,2,3,4,5,6,7}}               |             1 |             7 |          null
  2 | {{7,8,9},{10,11,12}}            |             2 |             3 |          null
  3 | {1,2,3,4,5}                     |             5 |          null |          null
  4 | {{{1},{2}},{{3},{4}},{{5},{6}}} |             3 |             2 |             1

In this example, the database contains arrays in the c3 column of the t1 table. I used array_length() against that column to get the length of each dimension of each array, up to three dimensions.

In some cases null was returned. This is because the specified dimension didn’t exist in that particular array.

Arrays Containing null

Any null values in the array are counted just as any other value is:

SELECT array_length(array[ null, null, null ], 1);

Result:

3

This array contained three null values and so we got a result of 3.

Specifying a null Dimension

Specifying null for the dimension results in null being returned:

SELECT array_length(array[ 'Ben', 'Jen', 'Len' ], null);

Result:

null