PostgreSQL：如何识别表中所有行均为NULL的列

你的当前SQL是逐行检查各行的空列并拼接标识，所以会返回不同行的空列组合，这不符合“找出整张表中所有行均为NULL的列”的需求。以下是正确的解决方案：

方法1：手动指定列检查（适合列数少的场景） #

通过COUNT(column)判断列是否全空（COUNT会自动忽略NULL值，结果为0则说明列中无有效数据）。

单列出结果 #

SELECT 'A' AS all_null_column FROM t HAVING COUNT(a) = 0
UNION ALL
SELECT 'B' FROM t HAVING COUNT(b) = 0
UNION ALL
SELECT 'C' FROM t HAVING COUNT(c) = 0
UNION ALL
SELECT 'D' FROM t HAVING COUNT(d) = 0;

针对示例表，该查询会仅返回A。

逗号分隔合并结果 #

如果希望将所有全空列标识合并为一行：

SELECT
  CONCAT_WS(',',
    CASE WHEN COUNT(a) = 0 THEN 'A' END,
    CASE WHEN COUNT(b) = 0 THEN 'B' END,
    CASE WHEN COUNT(c) = 0 THEN 'C' END,
    CASE WHEN COUNT(d) = 0 THEN 'D' END
  ) AS all_null_columns
FROM t;

方法2：动态生成SQL（适合40+列的大表） #

当表列数较多时，手动编写每列的判断逻辑效率极低，可以利用数据库的系统表自动生成查询语句：

MySQL 版本 #

-- 生成列判断逻辑
SELECT GROUP_CONCAT(
  CONCAT('CASE WHEN COUNT(`', column_name, '`) = 0 THEN ''', UPPER(column_name), ''' END')
  SEPARATOR ', '
) INTO @sql
FROM information_schema.columns
WHERE table_schema = '你的数据库名'
  AND table_name = 't';

-- 拼接完整查询并执行
SET @sql = CONCAT('SELECT CONCAT_WS(\',\', ', @sql, ') AS all_null_columns FROM t');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PostgreSQL 版本 #

DO $$
DECLARE sql_query text;
BEGIN
  SELECT string_agg(
    format('CASE WHEN COUNT(%I) = 0 THEN ''%s'' END', column_name, upper(column_name)),
    ', '
  ) INTO sql_query
  FROM information_schema.columns
  WHERE table_schema = 'public' -- 替换为你的schema
    AND table_name = 't';

  EXECUTE format('SELECT CONCAT_WS('','', %s) AS all_null_columns FROM t', sql_query);
END $$;

关于磁盘空间的补充 #

删除全空列是否释放磁盘空间取决于数据库存储引擎：

• MySQL InnoDB：固定长度字段（如INT）的全空列会占用存储空间，删除后会释放；可变长度字段（如VARCHAR）的全空列仅占用少量元数据空间，释放空间有限。
• PostgreSQL：全空列几乎不占用数据存储空间，仅在系统表中记录列信息，删除后主要减少元数据开销，对磁盘空间释放影响不大。

无论如何，识别全空列后能有效简化数据筛选、视图创建等后续操作。

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