Postgres - How to check for an empty array

user_161922 0
Viewed 66457

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[];
                                                             ^
postgresql arrays sql
5 Answers

The syntax should be:

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

You use quotes plus curly braces to show array literals.

Tom_H_56966080

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;
Admin1
user_59858530 
SELECT  COUNT(*)
FROM    table
WHERE   datasets = ARRAY(SELECT 1 WHERE FALSE)
user_551590
Related