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[];
^
The syntax should be:
SELECT
COUNT(*)
FROM
table
WHERE
datasets = '{}'
You use quotes plus curly braces to show array literals.
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
cardinality "returns the total number of elements in the array, or 0 if the array is empty", which is what you would expect of array_length, but the later returns NULL when length is zero.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 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}
SELECT COUNT(*)
FROM table
WHERE datasets = ARRAY(SELECT 1 WHERE FALSE)
