To count the number of elements in an array in PostgreSQL, one can use the cardinality() function or the array_length() function.
Counting the number of elements in array is achieved by two methods:
- cardinality(array): Returns the total number of elements in the array.
- array_length(array, 1): Returns the number of elements in the specified dimension (usually 1 for one-dimensional arrays).
Method 1: Count the number of Array elements using cardinality()
The cardinality() function returns the total number of elements in an array.
SELECT cardinality(ARRAY['Bread', 'Butter', 'Jam']) AS element_count;
Explanation: The array {‘Bread’, ‘Butter’, ‘Jam’} has 3 elements.
Output:
Method 2: Count the number of Array elements using array_length()
The array_length() function returns the number of elements along a specific dimension of the array. For a one-dimensional array, you would specify 1 as the dimension.
SELECT array_length(ARRAY['Bread', 'Butter', 'Jam'], 1) AS element_count;
Explanation: The array {‘Bread’, ‘Butter’, ‘Jam’} has 3 elements.
Output:
Count the number of Array Elements a Column in PostgreSQL:
Let’s use the table name “orders_arr_table”
You can count the number of elements in the order_items column of the PostgreSQL table.
Method 1: Count the number of Array elements of column using cardinality() function
The cardinality() function returns the total number of elements in a column of PostgreSQL table.
SELECT *, cardinality(order_items) AS item_count FROM orders_arr_table;
Explanation: The cardinality() function takes the entire “order_items” column and counts the number of elements of that particular column
Output:
Method 2: Count the number of Array elements of column using array_length() function
The array_length() function returns the total number of elements in a column of PostgreSQL table.
SELECT *, array_length(order_items,1) AS item_count FROM orders_arr_table;
Explanation: The array_length() function takes the entire “order_items” column and also argument 1 which denotes one dimensional array and counts the number of elements of that particular column
Output:
