在PostgreSQL中查找表的空列
发布时间:2020-12-13 16:23:44 所属栏目:百科 来源:网络整理
导读:什么查询将返回所有行为NULL的表的列的名称? 测试平台: 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 s
什么查询将返回所有行为NULL的表的列的名称?
测试平台:
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) (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |