Postgres - How to check for an empty array

Checking Array Bounds Using PostgreSQL Functions

For an alternative method, consider the array_upper and array_lower functions. These can identify an empty array by evaluating its bounds.

SELECT (array_upper(your_array, 1) IS NULL) AS is_empty FROM your_table;


Why does this work? The bounds of an empty array in PostgreSQL are NULL. Hence, array_upper returns NULL when there are no elements in the array, much like a party where nobody showed up. 🥳

Potholes on the Road: Common Mistakes to Avoid

Be cautious to not mistake ARRAY[NULL]::array_datatype or ARRAY(SELECT 1 WHERE FALSE) as symbols of an empty array. They either represent an array with NULL or a fancy way to complicate your query, respectively. Just like putting a "Beware of the Dog" sign doesn't mean you have a dog!

Literal Translation: Understanding Array Literals

In PostgreSQL, if we query for '{}', it is understood as an empty array. It's important to enclose array literals with proper quotation marks and braces:

SELECT '{}'::integer[] AS empty_int_array;


This is similar to handing in a blank paper when you're clueless during an exam. It's not wrong; it's just empty!

Version Discrepancies: Cardinality Availability

Consider the PostgreSQL version when using the cardinality function, as it was introduced only in PostgreSQL 9.4. If you're stuck with an earlier version, you're stuck with array_upper and array_lower, or other crafted solutions. Kind of like wanting an iPhone 13 when you still have the iPhone 4!

Ever-Present Presence-checks: Practical Usage of Empty Array Checks in Queries

When you're engaged in the practical world of SQL queries, you might find yourself needing to execute actions based on the emptiness of an array:

Let the Counting Games Begin: Counting Rows with Empty Arrays

SELECT COUNT(*) FROM your_table WHERE your_array = '{}';


I Smell Humans!: Filtering Out Empty Arrays

SELECT * FROM your_table WHERE NOT (your_array = '{}');


Empty? You're Out!: Conditional UPDATE/DELETE Operations

UPDATE your_table SET some_column = 'value' WHERE your_array = '{}';
DELETE FROM your_table WHERE your_array = '{}';


Just like in a horror movie, if you're empty, you're toast!

NULL-ifying the Problem: Dealing with NULLs in Arrays

Consider an important nuance when working with arrays that contain NULL values. An array with a NULL is different from an empty array:

SELECT ARRAY[NULL] IS NOT DISTINCT FROM ARRAY[]::integer[] AS has_nulls_but_not_empty;


This will return false because an array with a NULL is not entirely empty. It's like saying a box with only air isn't empty!