Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE OR REPLACE FUNCTION f_num_nulls(_tbl regclass)
  RETURNS SETOF int AS
$func$
BEGIN
   RETURN QUERY EXECUTE format(
      'SELECT num_nulls(%s) FROM %s'
    , (SELECT string_agg(quote_ident(attname), ', ')  -- column list
       FROM   pg_attribute
       WHERE  attrelid = _tbl
       AND    NOT attisdropped    -- no dropped (dead) columns
       AND    attnum > 0)         -- no system columns
    , _tbl
   );
END
$func$  LANGUAGE plpgsql;
TABLE pg_attribute LIMIT 3;  -- random (existing) system table with null values


attrelid
attname
atttypid
attstattarget
attlen
attnum
attndims
attcacheoff
atttypmod
attbyval
attstorage
attalign
attnotnull
atthasdef
attidentity
attisdropped
attislocal
attinhcount
attcollation
attacl
attoptions
attfdwoptions


1255
proname
19
-1
64
1
0
-1
-1
f
p
c
t
f

f
t
0
0
null
null
null

1255
pronamespace
26
-1
4
2
0
-1
-1
t
p
i
t
f

f
t
0
0
null
null
null

1255
proowner
26
-1
4
3
0
-1
-1
t
p
i
t
f

f
t
0
0
null
null
null


SELECT * FROM f_num_nulls('pg_attribute') LIMIT 3;


f_num_nulls


3

3

3


-- for anonymous records; with jsonb
SELECT *
FROM  (
   VALUES
      (NULL ,'hi',2,NULL,'null')
    , (NULL ,'hi',2,NULL,'null')  -- dupe!!
    , ('' ,'hi',2,3,'test')
    , (NULL ,'hi',2,3,'null')
   ) t   -- column names unknown
, LATERAL (
   SELECT count(*) FILTER (WHERE j.value = jsonb 'null') AS num_nulls
   FROM   jsonb_each(to_jsonb(t)) j
   ) c;


column1
column2
column3
column4
column5
num_nulls


null
hi
2
null
null
2

null
hi
2
null
null
2


hi
2
3
test
0

null
hi
2
3
null
1


-- Or  with json, probably faster.
-- Demonstrating 3 different ways
SELECT *
FROM  (
   VALUES
      (NULL ,'hi',2,NULL,'null')
    , (NULL ,'hi',2,NULL,'null')  -- dupe!!
    , ('' ,'hi',2,3,'test')
    , (NULL ,'hi',2,3,'null')
   ) t   -- column names unknown
, to_json(t) j
, LATERAL (
   SELECT count(*) FILTER (WHERE j1.value::text = 'null') AS num_nulls1
   FROM   json_each(to_json(t)) j1
   ) c1
, LATERAL (
   SELECT count(*) FILTER (WHERE j->>k IS NULL) AS num_nulls2
   FROM   json_object_keys(j) k
   ) c2
, LATERAL (
   SELECT count(*) - count(j->>k ) AS num_nulls3
   FROM   json_object_keys(j) k
   ) c3;


column1
column2
column3
column4
column5
j
num_nulls1
num_nulls2
num_nulls3


null
hi
2
null
null
{"column1":null,"column2":"hi","column3":2,"column4":null,"column5":"null"}
2
2
2

null
hi
2
null
null
{"column1":null,"column2":"hi","column3":2,"column4":null,"column5":"null"}
2
2
2


hi
2
3
test
{"column1":"","column2":"hi","column3":2,"column4":3,"column5":"test"}
0
0
0

null
hi
2
3
null
{"column1":null,"column2":"hi","column3":2,"column4":3,"column5":"null"}
1
1
1

attrelid	attname	atttypid	attstattarget	attlen	attnum	attcacheoff	atttypmod	attbyval	attstorage	attalign	attnotnull	atthasdef	attisdropped	attislocal	attacl	attoptions	attfdwoptions
1255	proname	19	-1	64	1	-1	-1	f	p	c	t	f	f	t	null	null	null
1255	pronamespace	26	-1	4	2	-1	-1	t	p	i	t	f	f	t	null	null	null
1255	proowner	26	-1	4	3	-1	-1	t	p	i	t	f	f	t	null	null	null

column1	column2	column3	column4	column5	num_nulls
null	hi	2	null	null	2
null	hi	2	null	null	2
	hi	2	3	test	0
null	hi	2	3	null	1