Postgres - How to check for an empty array
SqlArraysPostgresqlSql Problem Overview
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[];
^
Sql Solutions
Solution 1 - Sql
The syntax should be:
SELECT
COUNT(*)
FROM
table
WHERE
datasets = '{}'
You use quotes plus curly braces to show array literals.
Solution 2 - Sql
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;
Solution 3 - Sql
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
Solution 4 - Sql
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}
Solution 5 - Sql
SELECT COUNT(*)
FROM table
WHERE datasets = ARRAY(SELECT 1 WHERE FALSE)