Counting Array Length in PostgreSQL

When it comes to counting the elements in these arrays, PostgreSQL provides two useful functions: array_length and cardinality.

Using array_length

The array_length function is specifically designed to find the length of a specified dimension in an array. Here's how you can use it:

SELECT array_length(array_column, 1) FROM your_table;

This function takes two arguments: the array itself and the dimension of the array you want to measure. Since PostgreSQL arrays are 1-based, the second argument is typically 1 for one-dimensional arrays.

Using cardinality

Introduced in PostgreSQL 9.5, the cardinality function is a more straightforward approach to counting array elements, as it does not require specifying the dimension:

SELECT cardinality(array_column) FROM your_table;

This function simply returns the total number of elements in the array, regardless of its dimensions, making it more versatile for multi-dimensional arrays.

array_length vs cardinality

Both array_length and cardinality are useful for counting array elements, but they have different strengths. The array_length function is helpful when dealing with multi-dimensional arrays and you need to count elements in a specific dimension. On the other hand, cardinality offers a simpler and more direct approach, especially useful when you just need the total count of elements across all dimensions of an array. If you're using PostgreSQL 9.5 or later, cardinality might be the preferred choice due to its simplicity and versatility. However, for earlier versions or specific dimensional counts, array_length is necessary.