How to Use array_length Function in PostgreSQL

In PostgreSQL, use the array_length(array, dimension) function to find the number of elements in an array along a given dimension. The array is the array value, and dimension is an integer specifying which dimension's length you want to get.

Syntax

The array_length function takes two arguments:

• array: The array you want to measure.
• dimension: The dimension number (1 for first dimension, 2 for second, etc.) whose length you want.

The function returns an integer representing the size of the array along that dimension, or NULL if the array is empty or the dimension does not exist.

sql

array_length(anyarray, integer) RETURNS integer

Example

This example shows how to use array_length to get the number of elements in a one-dimensional and a two-dimensional array.

sql

SELECT array_length(ARRAY[10, 20, 30, 40], 1) AS length_1d;

SELECT array_length(ARRAY[[1,2,3],[4,5,6]], 1) AS rows,
       array_length(ARRAY[[1,2,3],[4,5,6]], 2) AS columns;

Output

 length_1d 
-----------
         4
(1 row)

 rows | columns 
------+---------
    2 |       3
(1 row)

Common Pitfalls

Common mistakes when using array_length include:

• Using a dimension number that does not exist in the array, which returns NULL.
• Confusing array_length with cardinality, which returns the total number of elements regardless of dimensions.
• Passing a non-array value, which causes an error.

Always check if the array is not NULL before calling array_length to avoid unexpected results.

sql

/* Wrong: dimension 2 does not exist in 1D array */
SELECT array_length(ARRAY[1,2,3], 2) AS wrong_length;

/* Right: use dimension 1 for 1D array */
SELECT array_length(ARRAY[1,2,3], 1) AS correct_length;

Output

 wrong_length 
--------------
              
(1 row)

 correct_length 
----------------
              3
(1 row)

Quick Reference

Function	Description	Example Usage	Returns
array_length(array, dimension)	Returns length of array along given dimension	array_length(ARRAY[1,2,3], 1)	3
cardinality(array)	Returns total number of elements in array	cardinality(ARRAY[1,2,3])	3
array_upper(array, dimension)	Returns upper bound index of dimension	array_upper(ARRAY[1,2,3], 1)	3
array_lower(array, dimension)	Returns lower bound index of dimension	array_lower(ARRAY[1,2,3], 1)	1

Key Takeaways

Use array_length(array, dimension) to get the size of an array along a specific dimension.

Dimension 1 is the first level of the array; higher dimensions apply to multi-dimensional arrays.

array_length returns NULL if the dimension does not exist or the array is empty.

Check array dimensions carefully to avoid NULL results or errors.

For total elements regardless of dimension, use cardinality(array) instead.