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

Answer 1

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;