PostgreSQL优化查询：剔除非ID列全为空的行

针对你需要维护大量列的场景，以下是几种无需手动罗列所有非ID列的解决方案，适配不同数据库环境：

一、原生行级检查（推荐，部分数据库支持） #

PostgreSQL #

利用* EXCLUDE语法直接排除ID列，检查剩余整行是否全为NULL：

SELECT id, num1, num2, ... -- 可直接写*或指定需要的列
FROM your_table
WHERE NOT (row(* EXCLUDE (id)) IS NULL);

MySQL/MariaDB #

借助CONCAT_WS特性（忽略NULL值，若所有列均为NULL则返回NULL）：

SELECT id, num1, num2, ...
FROM your_table
WHERE CONCAT_WS('', num1, num2, ...) IS NOT NULL;

注意：如果存在非NULL的空字符串，该方法会将其视为有效行保留；若需严格区分空字符串与NULL，不建议使用。

二、动态生成查询（通用所有数据库） #

通过查询系统元数据表自动拼接非ID列，避免手动维护列名列表：

SQL Server #

DECLARE @cols NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(column_name), ', ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'your_table' AND COLUMN_NAME != 'id';

DECLARE @sql NVARCHAR(MAX) = N'
SELECT id, ' + @cols + '
FROM your_table
WHERE COALESCE(' + @cols + ') IS NOT NULL;';

EXEC sp_executesql @sql;

PostgreSQL #

DO $$
DECLARE
  cols TEXT;
BEGIN
  SELECT string_agg(quote_ident(column_name), ', ')
  INTO cols
  FROM information_schema.columns
  WHERE table_name = 'your_table' AND column_name != 'id';

  EXECUTE format('
    SELECT id, %s
    FROM your_table
    WHERE COALESCE(%s) IS NOT NULL;', cols, cols);
END $$;

三、其他数据库适配 #

• Oracle：可通过SYS_CONNECT_BY_PATH拼接列名生成动态SQL，或使用NVL2组合检查；
• SQLite：可利用GROUP_CONCAT从PRAGMA table_info获取列名，生成动态查询。

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