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.