In PostgreSQL, a variety of array functions are available that assist us in working with the array data productively. Some frequently used array functions include the ARRAY_APPEND(), ARRAY_REMOVE(), ARRAY_REPLACE(), etc. In Postgres, CARDINALITY() is also a notable array function that counts the total number of array elements.
This article will demonstrate different use cases of the CARDINALITY() function using appropriate examples.
How to Use CARDINALITY() Function in PostgreSQL?
The CARDINALITY() function accepts a single or multi-dimensional array and retrieves the total number of elements present in that array. Here is the syntax for the CARDINALITY() function:
CARDINALITY(arr);
It retrieves an integer that represents the number of the array elements.
Let’s learn it practically.
Example 1: CARDINALITY() Function With 1-D Array
In the following example, a single dimensional array is passed to the “CARDINALITY()” function:
SELECT CARDINALITY(ARRAY['Joe', 'John', 'Mike', 'Seth']);
The output demonstrates that the input array has four elements.
Example 2: CARDINALITY() Function With Multi-Dimensional Array
Let’s learn how to find the array elements of the multi-dimensional array using the CARDINALITY() function:
SELECT CARDINALITY('[2:4][2:3]={{10,20},{70,90},{100,120}}'::INTEGER[]);
The output shows that the given multi-dimensional array has six elements.
Example 3: CARDINALITY() Function on Table’s Data
In this example, we will use an already created table named “std_info”:
SELECT * FROM std_info;
Let’s utilize the CARDINALITY() function on the “std_num” column of the “std_info” table:
SELECT std_name, std_num, CARDINALITY(std_num) FROM std_info;
The CARDINALITY() function retrieves the total elements of the std_num array.
That’s all from this Postgres guide.
Conclusion
In PostgreSQL, CARDINALITY() is an array function that counts the total number of array elements. It accepts a single or multi-dimensional array and retrieves the total number of elements present in that array. The return type of the stated function is INT. This post explained a complete guide on how to use the CARDINALITY() function in PostgreSQL.
