可以查询catalog表pg_attribute http://www.postgresql.org/docs/current/interactive/catalog-pg-attribute.html获取未定义的列的列表NOT NULL
因此can hold NULL
values:
SELECT quote_ident(attname) AS column_can_be_null
FROM pg_attribute
WHERE attrelid = 'tbl'::regclass -- valid, visible table name
AND attnum >= 1 -- exclude tableoid & friends
AND NOT attisdropped -- exclude dropped columns
AND NOT attnotnull -- exclude columns defined NOT NULL!
ORDER BY attnum;
Where tbl
是您的(可选模式限定的)表名。
并没有说该列中有任何实际的 NULL 值。您必须测试每一列。像这样:
具有 plpgsql 功能的完全自动化
CREATE OR REPLACE FUNCTION f_all_null_columns_of_tbl(_tbl regclass)
RETURNS SETOF text AS
$func$
DECLARE
_row_ct bigint; -- count rows in table $1
_sql text; -- SQL string to test for NULL values
_cols text[]; -- array of candidate column names
_nulls bool[]; -- array of test results
BEGIN
EXECUTE 'SELECT count(*) FROM ' || _tbl
INTO _row_ct;
IF _row_ct = 0 THEN
RAISE EXCEPTION 'Table % has no rows!', _tbl; -- pointless for empty table
ELSE
RAISE NOTICE '% rows in table %.', _row_ct, _tbl;
END IF;
SELECT INTO _sql, _cols
'SELECT ARRAY[' || string_agg('bool_and(' || col || ' IS NULL)', ', ')
|| '] FROM ' || _tbl
, array_agg(col)
FROM (
SELECT quote_ident(attname) AS col
FROM pg_attribute
WHERE attrelid = _tbl -- valid, visible table name
AND attnum >= 1 -- exclude tableoid & friends
AND NOT attisdropped -- exclude dropped columns
AND NOT attnotnull -- exclude columns defined NOT NULL!
ORDER BY attnum
) sub;
EXECUTE _sql INTO _nulls;
FOR i IN 1 .. array_upper(_cols, 1)
LOOP
IF _nulls[i] THEN -- column is NULL in all rows
RETURN NEXT _cols[i];
END IF;
END LOOP;
RETURN;
END
$func$ LANGUAGE plpgsql;
Call:
SELECT f_all_null_columns_of_tbl('my_schema.my_table');
使用 Postgres 9.1 和 9.3 进行测试。
这使用了许多高级 plpgsql 功能。
SQL 小提琴。 http://sqlfiddle.com/#!15/ec8ed/1
相关答案使用现代语法构建 SQL 代码并执行它:
- 将空字符串替换为 null 值 https://stackoverflow.com/questions/10621897/replace-blank-spaces-with-null-values/10686513#10686513
关于遍历记录:
- 循环记录的列 https://stackoverflow.com/questions/13065774/pl-pgsql-loop-through-columns-of-record/13079081#13079081