Postgres - How to check an empty array

Question

I am using Postgres and I am trying to write a query like this:

select count(*) from table where datasets = ARRAY[] 

i.e. I want to know how many rows an empty array has for a specific column, but postgres doesn't like it:

 select count(*) from super_eds where datasets = ARRAY[]; ERROR: syntax error at or near "]" LINE 1: select count(*) from super_eds where datasets = ARRAY[]; ^ 

Answer 1

The syntax should be:

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

You use quotation marks and curly braces to display array literals.

Answer 2

You can use the fact that the 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; 

Answer 3

 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} 

Answer 4

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