SQLearnAcademy
←Function reference
Arrays and seriesPostgreSQL only

ARRAY_LENGTH

Returns the length of an array in the given dimension.

Syntax

ARRAY_LENGTH(<array>, <dimension>)

For an ordinary list the dimension is 1: ARRAY_LENGTH(tags, 1). It differs from CARDINALITY in two ways: it asks about a specific dimension, and for an empty array it returns NULL, not 0.

The length of a list
SELECT ARRAY_LENGTH(ARRAY['sql', 'postgres', 'database'], 1) AS tag_count;
tag_count
3
An empty array: CARDINALITY versus ARRAY_LENGTH
SELECT CARDINALITY(ARRAY[]::text[]) AS cardinality,
ARRAY_LENGTH(ARRAY[]::text[], 1) AS array_length;
cardinalityarray_length
0NULL

For an empty array, CARDINALITY gives 0 and ARRAY_LENGTH gives NULL.

Rows and columns of a two-dimensional array
SELECT ARRAY_LENGTH(ARRAY[[1, 2, 3], [4, 5, 6]], 1) AS rows_count,
ARRAY_LENGTH(ARRAY[[1, 2, 3], [4, 5, 6]], 2) AS columns_count;
rows_countcolumns_count
23

Watch out

For an empty array it's NULL, so ARRAY_LENGTH(tags, 1) = 0 never works. Check IS NULL or use CARDINALITY(tags) = 0.

The dimension is required: ARRAY_LENGTH(ARRAY['a']) throws function array_length(text[]) does not exist.

A dimension the array doesn't have gives NULL: ARRAY_LENGTH(ARRAY['a'], 2).

Learn more in the course

Arrays and ENUM