sql - 如何获取表中每一列的空值计数

Question

我有一个包含 20 列的表。我如何知道是否有任何列包含空值。如果有空值，如何计算它们。

Answer 1

使用jsonb functions:

create table my_table(id int primary key, val numeric, str text, day date);
insert into my_table values
(1, 10, 'a', '2018-01-01'),
(2, 20, 'b', null),
(3, 30, null, null),
(4, null, null, null);

select key as column, count(*) as null_values
from my_table t
cross join jsonb_each_text(to_jsonb(t))
where value is null
group by key;

 column | null_values 
--------+-------------
 val    |           1
 str    |           2
 day    |           3
(3 rows)        

Working example in rextester.