Postgres - How to check for an empty array

Question

I'm using Postgres and I'm trying to write a query like this:

select count(*) from table where datasets = ARRAY[]

i.e. I want to know how many rows have an empty array for a certain column, but postgres doesn't like that:

select count(*) from super_eds where datasets = ARRAY[];
ERROR:  syntax error at or near "]"
LINE 1: select count(*) from super_eds where datasets = ARRAY[];
                                                             ^

Answer 1

The syntax should be:

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

You use quotes plus curly braces to show array literals.

Answer 2

SELECT  COUNT(*)
FROM    table
WHERE   datasets = ARRAY(SELECT 1 WHERE FALSE)

Answer 3

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;