PostgreSQL中如何检测表中多列NULL值并筛选对应含NULL的行？

问题分析 #

你使用num_nulls()函数能筛选出至少包含一个NULL值的行，但SELECT *会返回所有列（包括无NULL的company列）。要实现只展示存在NULL的列及对应行，核心是先识别哪些列包含NULL，再动态生成仅包含这些列的查询语句。

方法1：先定位含NULL的列，再手动生成查询 #

首先执行以下语句，获取表中所有包含至少一个NULL值的列名：

SELECT column_name
FROM information_schema.columns
WHERE table_name = 'mpe'
  AND table_schema = 'public' -- 替换为你的表所在schema
  AND EXISTS (
    SELECT 1
    FROM mpe
    WHERE column_name IS NULL
  );

比如这个查询会返回city、st、dbas（假设这些列存在NULL），而company不会出现在结果中。

接着用这些列名拼接成查询语句，只选择目标列并过滤含NULL的行：

SELECT city, st, dbas 
FROM mpe 
WHERE num_nulls(city, st, dbas) > 0;

方法2：用动态SQL自动生成并执行查询 #

如果不想手动拼接，可以借助PostgreSQL的动态SQL自动完成：

方式A：生成查询语句后手动执行

WITH null_columns AS (
  SELECT string_agg(column_name, ', ') AS cols
  FROM information_schema.columns
  WHERE table_name = 'mpe'
    AND table_schema = 'public'
    AND EXISTS (
      SELECT 1
      FROM mpe
      WHERE column_name IS NULL
    )
)
SELECT format('SELECT %s FROM mpe WHERE num_nulls(%s) > 0;', cols, cols) AS query
FROM null_columns;

执行后会输出一条完整的查询语句，直接复制执行即可得到目标结果。

方式B：用DO块自动执行（适合快速验证，无直接结果返回）

DO $$
DECLARE
  cols text;
BEGIN
  SELECT string_agg(column_name, ', ') INTO cols
  FROM information_schema.columns
  WHERE table_name = 'mpe'
    AND table_schema = 'public'
    AND EXISTS (
      SELECT 1
      FROM mpe
      WHERE column_name IS NULL
    );

  IF cols IS NOT NULL THEN
    EXECUTE format('SELECT %s FROM mpe WHERE num_nulls(%s) > 0;', cols, cols);
  ELSE
    RAISE NOTICE '表mpe中没有包含NULL值的列';
  END IF;
END $$;

方式C：创建函数返回结构化结果

如果需要直接返回可读的结构化结果，可以创建一个函数：

CREATE OR REPLACE FUNCTION get_null_columns_and_rows(p_table_name text, p_schema_name text DEFAULT 'public')
RETURNS SETOF jsonb AS $$
DECLARE
  cols text;
  query text;
BEGIN
  SELECT string_agg(column_name, ', ') INTO cols
  FROM information_schema.columns
  WHERE table_name = p_table_name
    AND table_schema = p_schema_name
    AND EXISTS (
      SELECT 1
      FROM pg_catalog.pg_class c
      JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
      WHERE c.relname = p_table_name
        AND n.nspname = p_schema_name
        AND (c.relkind = 'r' OR c.relkind = 'v')
        AND column_name IS NULL
    );

  IF cols IS NULL THEN
    RETURN;
  END IF;

  query := format('SELECT to_jsonb(t) FROM (SELECT %s FROM %I.%I WHERE num_nulls(%s) > 0) t;', cols, p_schema_name, p_table_name, cols);
  RETURN QUERY EXECUTE query;
END $$ LANGUAGE plpgsql;

调用函数获取结果：

SELECT * FROM get_null_columns_and_rows('mpe');

返回的是JSONB格式数据，每个对象仅包含含NULL的列及对应行的内容。

内容的提问来源于stack exchange，提问作者Ramsey A.