Postgresql – Finding empty columns of a table in PostgreSQL

database-designnullpostgresqlschema

What query would return the name of the columns of a table where all rows are NULL?

Best Answer

testbed:

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;

function:

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;$$;

query:

select table_schema, table_name, column_name, 
       has_nonnulls(table_schema, table_name, column_name)
from information_schema.columns
where table_schema='stack';

result:

 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)

In addition you can get an approximate answer by querying the catalog - if null_frac is zero that indicates no nulls but should be double-checked against 'real' data:

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)
Related Question