Count null values in each column of a table : PSQL

user_12709265 0
Viewed 274

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

postgresql-14 postgresql
2 Answers

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;
user_3303150

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;
user_11835140
Related