Count null values in each column of a table : PSQL

Question

I have a very big table but as an example I will only provide a very small part of it as following:-

col1     col2     col3     col4
           10        2       12
  13        4                11
            0        1         
            3        5      111

I know how to find null values in one column. What I want to find is how many null values are there in each column just by writing one query.

Thanks in advance

`COUNT(*)-COUNT(col1)` or `COUNT(*) FILTER (WHERE col1 IS NULL)`. Repeat for each column. — Bergi, Feb 26 '22 at 17:16

score 4 · Accepted Answer · answered Feb 26 '22 at 17:15

4

You can use an aggregate with a filter:

select count(*) filter (where col1 is null) as col1_nulls,
       count(*) filter (where col2 is null) as col2_nulls,
       count(*) filter (where col3 is null) as col3_nulls,
       count(*) filter (where col4 is null) as col4_nulls
from the_table;

answered Feb 26 '22 at 17:15

Isn't there an easier way to do it? In my actual table I have 77 columns, so I don't want to write one line again and again. – Shawn Brar Feb 26 '22 at 17:18
You should have mentioned that in your question. But that's a one time thing to write, quite easily done with some copy & paste (and probably doesn't take longer that writing the question here on SO). If you need that query frequently, then just put it into a view. – Feb 26 '22 at 17:22

score 0 · Answer 2 · answered Feb 27 '22 at 08:55

I think you can generate this query on the fly. Here is an example of one way you can approach it:

CREATE OR REPLACE FUNCTION null_counts(tablename text)
  RETURNS SETOF jsonb LANGUAGE plpgsql AS
$func$
BEGIN
RETURN QUERY EXECUTE 'SELECT to_jsonb(t) FROM (SELECT ' || (
    SELECT string_agg('count(*) filter (where ' || a.attname::text || ' is null) as ' || a.attname || '_nulls', ',')
    FROM   pg_catalog.pg_attribute a 
    WHERE  a.attrelid = tablename::regclass
    AND    a.attnum > 0
    AND    a.attisdropped = false
    ) || ' FROM   ' || tablename::regclass || ') as t';
END
$func$; 

SELECT null_counts('your_table') AS val;

Count null values in each column of a table : PSQL

2 Answers2