Postgres - How to check for an empty array

The syntax should be:

SELECT
     COUNT(*)
FROM
     table
WHERE
     datasets = '{}'

You use quotes plus curly braces to show array literals.

You can use the fact that array_upper and array_lower functions, on empty arrays return null , so you can:

select count(*) from table where array_upper(datasets, 1) is null;

If you find this question in 2020, like I did, the correct answer is

select count(*) from table where cardinality(datasets) = 0

cardinality was added in PostgreSQL 9.4, which is ~2015

https://www.postgresql.org/docs/9.4/functions-array.html

Related