CODEWITHSUNDEEP
sqlarrayspostgresql
Amandasaurus
Amandasaurus

Reputation: 60809

Postgres - How to check for an empty array

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[];
                                                             ^

Upvotes: 84

Views: 84295

Answers (5)

Alexey Soshin
Alexey Soshin

Reputation: 17731

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

Upvotes: 26

WorkerBee
WorkerBee

Reputation: 67

Solution Query:

select id, name, employee_id from table where array_column = ARRAY[NULL]::array_datatype;

Example:

table_emp:

id (int)| name (character varying) | (employee_id) (uuid[])
1       | john doe                 | {4f1fabcd-aaaa-bbbb-cccc-f701cebfabcd, 2345a3e3-xxxx-yyyy-zzzz-f69d6e2edddd }
2       | jane doe                 | {NULL}


select id, name, employee_id from tab_emp where employee_id = ARRAY[NULL]::uuid[];

    -------
2       | jane doe                 | {NULL}

Upvotes: 5

user80168
user80168

Reputation:

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;

Upvotes: 21

Quassnoi
Quassnoi

Reputation: 425823

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

Upvotes: 1

Tom H
Tom H

Reputation: 47402

The syntax should be:

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

You use quotes plus curly braces to show array literals.

Upvotes: 116

Related Questions