Postgres - How to check for an empty array

The syntax should be:

SELECT     COUNT(*)FROM     tableWHERE     datasets = '{}'

You use quotes plus curly braces to show array literals.

sql arrays postgresql

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;

sql arrays postgresql

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

CodeHunter