SQL Postgres - 如何检查空数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/737669/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres - How to check for an empty array
提问by Rory
I'm using Postgres and I'm trying to write a query like this:
我正在使用 Postgres，我正在尝试编写这样的查询：
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:
即我想知道某列有多少行有一个空数组，但 postgres 不喜欢这样：
select count(*) from super_eds where datasets = ARRAY[];
ERROR: syntax error at or near "]"
LINE 1: select count(*) from super_eds where datasets = ARRAY[];
^
回答by Tom H
The syntax should be:
语法应该是：
SELECT
COUNT(*)
FROM
table
WHERE
datasets = '{}'
You use quotes plus curly braces to show array literals.
您可以使用引号加上花括号来显示数组文字。
回答by Rory
You can use the fact that array_upper and array_lower functions, on empty arrays return null , so you can:
您可以使用 array_upper 和 array_lower 函数在空数组上返回 null 的事实，因此您可以：
select count(*) from table where array_upper(datasets, 1) is null;
回答by WorkerBee
Solution Query:
select id, name, employee_id from table where array_column = ARRAY[NULL]::array_datatype;
Example:
table_emp:
表_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}
回答by Alexey Soshin
If you find this question in 2020, like I did, the correct answer is
如果你像我一样在 2020 年发现这个问题，正确答案是
select count(*) from table where cardinality(datasets) = 0
cardinalitywas added in PostgreSQL 9.4, which is ~2015
cardinality在 PostgreSQL 9.4 中添加，即 ~2015
回答by Quassnoi
SELECT COUNT(*)
FROM table
WHERE datasets = ARRAY(SELECT 1 WHERE FALSE)
