CARDINALITY

The CARDINALITY function returns the size or the count of all the elements in an array. It can be a useful function to understand how many elements are in an array and often it is used a check to make sure that operations on arrays are valid. Below we look at how to use the CARDINALITY function

SYNTAX

The cardinality function take in an array as the only argument.

CARDINALITY(some_array)

CARDINALITY by Example

The table below contains information about players and the points they got in a championship. The output of the table looks like this:

SELECT *
FROM BoardGamePoints;

OUTPUT player | points -------+--------------- Lato | {4,5,1} Mario | {9,1,5} Kalie | {7,2,3} Leo | {5,2,3,9} Jeny | {5,2,0,1,2,4} Pro | {1,2,0,1,4} (6 rows)

Let's write a SQL query that will return the number of games each player was involved in the tournament.

SELECT
    *,
    CARDINALITY(points) AS games
from BoardGamePoints;

OUTPUT player | points | games -------+---------------+------- Lato | {4,5,1} | 3 Mario | {9,1,5} | 3 Kalie | {7,2,3} | 3 Leo | {5,2,3,9} | 4 Jeny | {5,2,0,1,2,4} | 6 Pro | {1,2,0,1,4} | 5 (6 rows)

Notice that the output now contains the count of the size of the arrays.