A Comprehensive Guide to Checking if an Array is Empty in PostgreSQL

Introduction

In PostgreSQL database development, arrays are a commonly used data structure, but correctly detecting whether an array is empty or NULL often poses challenges for developers when handling dynamic queries. This article systematically addresses this issue based on real-world cases and provides multiple solutions.

Problem Context

Consider a PostgreSQL function with a parameter that is an integer array id_clients. When constructing dynamic SQL queries, if the array is empty, the related condition should not be included in the query. For example, when id_clients is '{}', the generated query should not contain an invalid condition like AND id = ANY('{}').

Common Incorrect Approaches

Many developers attempt to use ARRAY_UPPER(id_clients) IS NOT NULL or ARRAY_LENGTH(id_clients) to check if an array is empty, but this leads to errors because the array_length() function requires two parameters: the array itself and its dimension. For instance, array_length(id_clients, 1) returns the length of the first dimension of the array, or NULL if the array is empty.

Correct Solutions

Using the array_length() Function

The array_length() function is one standard method for detecting empty arrays. Its syntax is array_length(array, dimension), where dimension specifies the array's dimension (typically 1). For example:

IF array_length(id_clients, 1) > 0 THEN
    query := query || format(' AND id = ANY(%L)', id_clients);
END IF;

This approach excludes both empty arrays and NULL values, as array_length(NULL, 1) returns NULL, and NULL > 0 evaluates to NULL, which is treated as false in an IF condition.

Using the cardinality() Function

In PostgreSQL 9.4 and later, the cardinality() function offers a more concise solution. This function returns the total number of elements in an array, with empty arrays returning 0. For example:

IF cardinality(id_clients) > 0 THEN
    query := query || ' AND id = ANY(' || quote_nullable(id_clients) || ')';
END IF;

Compared to array_length(), cardinality() does not require specifying a dimension, making the code cleaner. However, note that cardinality() may not be available in older versions.

Direct Comparison with an Empty Array

If only checking whether an array is empty (excluding NULL), you can directly compare it to an empty array literal:

IF id_clients <> '{}' THEN
    query := query || ' AND id = ANY(' || quote_nullable(id_clients) || ')';
END IF;

This method returns true if the array is empty, NULL if it is NULL, and false if it contains elements. In practice, since both empty arrays and NULL often need handling, the first two methods are more commonly used.

Best Practices for Dynamic SQL Construction

When building dynamic queries in stored procedures, direct string concatenation can introduce SQL injection risks. It is recommended to use the EXECUTE ... USING clause to pass parameters, improving security and performance. For example:

EXECUTE format('SELECT * FROM table WHERE date BETWEEN $1 AND $2 %s',
               CASE WHEN array_length(id_clients, 1) > 0 THEN ' AND id = ANY($3)' ELSE '' END)
USING _start_date, _stop_date, id_clients;

This approach avoids string concatenation and allows PostgreSQL to optimize the query plan.

Performance and Compatibility Considerations

array_length() and cardinality() have similar performance, but cardinality() offers cleaner code. For compatibility, if the application needs to support PostgreSQL versions below 9.4, use array_length(). Additionally, for multi-dimensional arrays, array_length() can specify dimensions, while cardinality() returns the total element count; choose based on specific needs.

Conclusion

To check if an array is empty in PostgreSQL, it is recommended to use array_length(id_clients, 1) > 0 or cardinality(id_clients) > 0, both of which effectively handle empty arrays and NULL values. When constructing dynamic queries, prioritize EXECUTE ... USING for enhanced security. By correctly applying these methods, developers can write more robust and efficient database functions.