在 PostgreSQL 中查找表的空列

什麼查詢會返回所有行都為 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,9) as id, 1 as val1, 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, table_name, 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)

在 Postgresql 中，您可以直接從統計數據中獲取數據：

vacuum analyze; -- if needed

select schemaname, tablename, attname
from pg_stats
where most_common_vals is null
and most_common_freqs is null
and histogram_bounds is null
and correlation is null
and null_frac = 1;

您可能會得到一些誤報，因此在找到候選人後需要重新檢查。

引用自：https://dba.stackexchange.com/questions/2714