vote up 2 vote down star

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[];
                                                             ^
flag
52% accept rate

3 Answers

vote up 3 vote down check

The syntax should be:

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

You use quotes plus curly braces to show array literals.

link|flag
vote up 1 vote down

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;
link|flag
vote up 0 vote down 
SELECT  COUNT(*)
FROM    table
WHERE   datasets = ARRAY(SELECT 1 WHERE FALSE)
link|flag
Although that might work, it just looks a bit messy. – Rory Apr 10 at 14:08

Your Answer

Get an OpenID
or
never shown

Not the answer you're looking for? Browse other questions tagged or ask your own question.