PostgreSQL 统计表各列非空值数量并输出column_name|count格式方法

方案1：手动拼接SQL（适合列少的场景） #

count()函数传入指定列名时会自动忽略该列的NULL值，你可以通过UNION ALL拼接所有列的统计结果，示例如下（将your_table替换为实际表名）：

SELECT 'col1' AS column_name, count(col1) AS count FROM your_table
UNION ALL
SELECT 'col2' AS column_name, count(col2) AS count FROM your_table
-- 剩余列按上述格式依次拼接即可
UNION ALL
SELECT 'col86' AS column_name, count(col86) AS count FROM your_table;

86列手动拼接效率较低，更推荐使用下面的动态SQL方案。

方案2：动态SQL（推荐，无需手动列全所有列） #

直接执行以下SQL即可自动获取所有列的非空值统计结果，执行前将public替换为表所属的schema名，your_table替换为实际表名即可：

WITH column_list AS (
  SELECT column_name
  FROM information_schema.columns
  WHERE table_schema = 'public'
    AND table_name = 'your_table'
  ORDER BY ordinal_position
)
SELECT
  jsonb_each_text.key AS column_name,
  jsonb_each_text.value::bigint AS count
FROM (
  SELECT jsonb_object_agg(
           column_name,
           (xpath('/row/c/text()', query_to_xml(format('SELECT count(%I) AS c FROM your_table', column_name), false, true, '')))[1]::text::bigint
         ) AS counts
  FROM column_list
) t, jsonb_each_text(t.counts);

执行后会直接返回column_name（列名）和count（对应列非空值数量）两列结果，完全匹配需求。如果你的表名/列名包含大写字符或特殊符号，注意按PostgreSQL规则用双引号包裹标识符即可。

内容的提问来源于stack exchange，提问作者John Doe