测试平台:
create role stack; create schema authorization stack; set role stack; create table my_table as select generate_series(0,9) as id,1 as val1,null::integer as val2; create table my_table2 as select generate_series(0,null::integer as val2,3 as val3;
功能:
create function has_nonnulls(p_schema in text,p_table in text,p_column in text) returns boolean language plpgsql as $$ declare b boolean; begin execute 'select exists(select * from '|| p_table||' where '||p_column||' is not null)' into b; return b; end;$$;
查询:
select table_schema,table_name,column_name,has_nonnulls(table_schema,column_name) from information_schema.columns where table_schema='stack';
结果:
table_schema | table_name | column_name | has_nonnulls --------------+------------+-------------+-------------- stack | my_table | id | t stack | my_table | val1 | t stack | my_table | val2 | f stack | my_table2 | id | t stack | my_table2 | val1 | t stack | my_table2 | val2 | f stack | my_table2 | val3 | t (7 rows)
此外,您可以通过查询目录获得一个近似答案 – 如果null_frac为零,表示没有空值,但应对“真实”数据进行双重检查:
select tablename,attname,null_frac from pg_stats where schemaname='stack'; tablename | attname | null_frac -----------+---------+----------- my_table | id | 0 my_table | val1 | 0 my_table | val2 | 1 my_table2 | id | 0 my_table2 | val1 | 0 my_table2 | val2 | 1 my_table2 | val3 | 0 (7 rows)