How to Get Array Length in PostgreSQL

1. the PostgreSQL array_length() Functionality
2. the PostgreSQL cardinality() Functionality
3. Use the array_length() Function to Get the Array Length in PostgreSQL
4. Use the cardinality() Function to Get the Array Length in PostgreSQL
5. Conclusion

This article describes calculating the length of an array using either of the two functions in PostgreSQL: array_length() and cardinality(). Both of these functions are described in detail with several examples in PostgreSQL.

Arrays have been used in PostgreSQL as a datatype. Numerous functionalities are associated with the use of arrays.

Everything is covered by the functionalities supported in PostgreSQL, from calculating the array length to searching for a specific array element.

the PostgreSQL array_length() Functionality

One of the two essential functions that help in the calculation of the length of an array is the array_length() function. It is the most basic function that calculates the length of any array using two parameters: the array and the corresponding array dimension for which length has to be calculated (if the array is multi-dimensional.

This article includes examples to calculate the length for a single and a multi-dimensional array using the array_length() functionality.

the PostgreSQL cardinality() Functionality

The second function in PostgreSQL used to calculate an array’s length is the cardinality() function. If you are running PostgreSQL version 9.4 or higher, the software will support the cardinality() functionality.

It works by receiving one parameter: the array. The function returns an integer value stating the total number of elements in the array.

Examples of using the cardinality() functionality are provided below.

Use the array_length() Function to Get the Array Length in PostgreSQL

The array_length() function can be used for single and multi-dimensional arrays. The following examples clarify the working of the function.

Example 1

Here is the first example that calculates the length of a single-dimensional array:

SELECT array_length(ARRAY[1,2,3], 1);

The above query will generate the output below:

Example 2

Let’s demonstrate the behavior of the array_length() function when it receives an empty array as a parameter:

SELECT array_length(ARRAY[]::int[], 1);

In this case, you might assume the output is zero; however, the function returns a NULL value. This is because the array_length() is designed to give a NULL value whenever it receives an empty array.

The query result is as follows:

Example 3

Similar behavior is shown by the array_length() function as above when it receives an array with missing dimensions, as shown below:

SELECT array_length(ARRAY[1,2,3], 2);

In this case, the function received a single dimension array as a parameter; however, it asks the function to return the size of the array’s second dimension. This produces a NULL value.

Note: The array_length() function produces the same value when it receives an empty array or an array with a missing dimension. This makes it challenging for the user to detect the error in case of a missing array dimension.

The output to the example is attached below:

Example 4

Let’s discuss the working of the function on a multi-dimensional array. The two queries below provide a complete insight into the array_length() function.

Query 1:

A two-dimensional array is passed as a parameter to the array_length() function. It is supposed to calculate the length of the first dimension:

SELECT array_length(ARRAY[[1, 2], [3, 4], [5, 6]], 1);

The query generates the following result:

Query 2:

The second query passes the same array to the array_length() function as a parameter; however, it asks for the length of the second dimension of the array:

SELECT array_length(ARRAY[[1, 2], [3, 4], [5, 6]], 2);

The query generates the following result:

Note: The two queries are an excellent example of understanding the working of the array_length() function with a multi-dimensional array.

Use the cardinality() Function to Get the Array Length in PostgreSQL

The cardinality() function can be used for a single and multi-dimensional array. Some examples are attached below that clarify the working of the function.

Identical arrays are used as parameters for the cardinality() function. This is to help you understand the working of the cardinality() function and, simultaneously, differentiate between the two methods to calculate the length of the array.

Example 1

We used the same array as in Example 1 under the array_length() function section.

SELECT cardinality(ARRAY[1,2,3]);

The query calculates the total number of elements and displays the expected result, as shown below:

Example 2

The cardinality() function works differently when an empty array is passed as a parameter. Contrary to array_length(), the cardinality() function displays a zero in case of an empty array.

SELECT cardinality(ARRAY[]::int[]);

The result of the query is attached below:

Example 3

A single-dimensional text-based array has been passed as a parameter to the cardinality() function, and it works as expected:

SELECT cardinality(ARRAY['hello', 'to', 'example', 'three']);

The result of the query is attached below:

Example 4

Moving to the functionality of cardinality() with a multi-dimensional array, it returns the total number of elements in each array dimension.

SELECT cardinality(ARRAY[[1, 2], [3, 4], [5, 6]]);

The two-dimensional array has a total of six elements; hence, the cardinality() function should also return the same number, as shown:

Example 5

A text-based multi-dimensional array works similarly to an integer-based multi-dimensional array.

SELECT cardinality(ARRAY[['hello'], ['there']]);

The total number of elements is returned by the cardinality() function:

Conclusion

Both array_length() and cardinality() functions calculate the length of an array; however, both work separately. Numerous examples have been highlighted throughout the article to display the differences between the two functions.