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

68688 次浏览

小开

最佳答案

The syntax should be:

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

You use quotes plus curly braces to show array literals.

小开

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

小开

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}

小开

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