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 66 · 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 — , Jun 24 '15 at 19:15
Of course its pg 8.2 so redshift dont got it... oh well have to list all my columns... — user433342, May 31 '23 at 01:13

score 0 · Answer 2 · answered Jun 27 '23 at 07:58

After getting the list of column and table, you can use LOOP and EXECUTE to run them one by one.

This wouldn't give you the answer in table form, but at least you can eyeball it via the RAISE NOTICEs, as for my use case, it's only part of a maintenance/patching work.

DO $$ 
DECLARE 
    id_columns TEXT;
    id_column_tables TEXT;
    null_count INTEGER;
BEGIN
    FOR id_columns, id_column_tables IN (
        SELECT column_name, table_name
        FROM information_schema.columns 
        WHERE table_name = 'A' 
    )
    LOOP
        EXECUTE FORMAT(
            'SELECT COUNT(*) FROM %I WHERE %I is null',
            id_column_tables,
            id_columns
        ) INTO null_count;
        
        IF null_count > 0 then
            RAISE NOTICE 'Column: % in TABLE: % - Null Count: %', id_columns, id_column_tables, null_count;
        END IF;
    END LOOP;
END $$;

This looks really interesting but all I ever get is `Query 1 OK: DO`. — iconoclast, Jun 29 '23 at 02:50
I'm not 100% sure on this, but the RAISE NOTICE seems to only print stuff when using a GUI like PgAdmin. I tried the query myself using psql and yeah, it didn't work — Imaduddin, Jul 03 '23 at 01:26

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

2 Answers2

Linked