sql - How to find all rows with a NULL value in any column using PostgreSQL

Question

There are many slightly similar questions, 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.

How can I select only the rows that have NULL values in any column?

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:

select* from A where (
  select column_name from information_schema.columns where table_name = 'A';
) IS NULL;

And searching has not turned up anything useful.

Answer 1

You can use 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.

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)