A Quick Overview of the CARDINALITY() Function in PostgreSQL

In PostgreSQL we can use the cardinality() function to return the total number of elements in an array. This includes elements across all dimensions of the array.

We pass the array as an argument to the function, and it returns an integer of the total number of elements in that array.

Example

Here’s a basic example to demonstrate:

SELECT cardinality(ARRAY[ 1, 2, 3 ]);

Result:

3

In this case I passed a one dimensional array and the cardinality() function returned the total number of elements in that array.

Multi Dimensional Arrays

The cardinality() function includes all dimensions of an array in its calculation.

Here’s a two dimensional array:

SELECT cardinality(ARRAY[ ARRAY[1, 2, 3], ARRAY[4, 5, 6] ]);

Result:

6

Here’s a three dimensional array:

SELECT cardinality(ARRAY[ ARRAY[ ARRAY[1, 2, 3], ARRAY[4, 5, 6] ] ]);

Result:

6

Same result because, even though the array had one more dimension, the number of elements didn’t change.

Here’s what happens if we increase the number of elements:

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

Result:

9

As expected, an array with nine elements returns 9.

Empty Arrays

If we pass an empty array, cardinality() returns 0:

SELECT cardinality(ARRAY[ ]::integer[]);

Result:

0

In this case I had to explicitly cast the array as an integer[] because being an empty array, there’s nothing to tell PostgreSQL what the type is, and arrays must have a type. If I hadn’t done this, I would have gotten an error.

Passing Non Arrays

Passing a non array as an argument results in an error:

SELECT cardinality( 1 );

Result:

ERROR:  function cardinality(integer) does not exist
LINE 1: SELECT cardinality( 1 );
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.