SQL 如何使用 PostgreSQL 在任何列中查找具有 NULL 值的所有行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31034847/
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
How to find all rows with a NULL value in any column using PostgreSQL
提问by iconoclast
There are many slightly similarquestions, but none solve precisely this problem. "Find All Rows With Null Value(s) in Any Column" is the closest one I could find and offers an answer for SQL Server, but I'm looking for a way to do this in PostgreSQL.
有许多稍微相似的问题，但没有一个能准确地解决这个问题。“在任何列中查找具有空值的所有行”是我能找到的最接近的行，并为 SQL Server 提供了答案，但我正在寻找一种在 PostgreSQL 中执行此操作的方法。
How can I select only the rows that have NULL values in anycolumn?
如何仅选择任何列中具有 NULL 值的行？
I can get all the column names easily enough:
我可以很容易地获得所有列名：
select column_name from information_schema.columns where table_name = 'A';
but it's unclear how to check multiple column names for NULL values. Obviously this won't work:
但不清楚如何检查多个列名的 NULL 值。显然这行不通：
select* from A where (
select column_name from information_schema.columns where table_name = 'A';
) IS NULL;
And searchinghas not turned up anything useful.
而搜索尚未打开了任何有用的东西。
回答by Marth
You can use NOT(<table> IS NOT NULL).
您可以使用NOT(<table> IS NOT NULL).
From the documentation:
从文档：
If the expression is row-valued, then IS NULL is true when the row expression itself is null or when all the row's fields are null, while IS NOT NULL is true when the row expression itself is non-null and all the row's fields are non-null.
如果表达式是行值，那么当行表达式本身为空或所有行的字段为空时，IS NULL 为真，而当行表达式本身为非空且所有行的字段都为空时，IS NOT NULL 为真非空。
So :
所以 ：
SELECT * FROM t;
┌────────┬────────┐
│ f1 │ f2 │
├────────┼────────┤
│ (null) │ 1 │
│ 2 │ (null) │
│ (null) │ (null) │
│ 3 │ 4 │
└────────┴────────┘
(4 rows)
SELECT * FROM t WHERE NOT (t IS NOT NULL);
┌────────┬────────┐
│ f1 │ f2 │
├────────┼────────┤
│ (null) │ 1 │
│ 2 │ (null) │
│ (null) │ (null) │
└────────┴────────┘
(3 rows)
