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.

There is another previous answer, that's much closer (actually answers your question - same solution that @Marth has posted)): http://stackoverflow.com/a/21026085/939860 — Erwin Brandstetter, Jul 16 '15 at 13:30
That does appear to be similar (although it's hard to understand because the questioner never poses a simple form of the question directly) but it's a bit different because it's about *specific* columns being NOT NULL rather than *any* columns being NOT NULL. Nonetheless it is very useful to know that question is there. Thanks! — iconoclast, Jul 16 '15 at 18:55

score 58 · Accepted Answer · edited Jun 24 '15 at 20:17

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)

I was thinking about `row_to_json()` but this is much better — a_horse_with_no_name, Jun 24 '15 at 19:15

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

1 Answers1

Linked