PostgreSQL 9.6 – Query to Get Counts of Values Per Column

countpostgresqlpostgresql-9.6

I have a big table of vendor-supplied data (that I can't change around much) with about 315 columns. I suspect that many of the columns are not being used (or at least not consistently).

I'd like a query that can give me the count per column of the values in the table.

For example

CREATE TABLE foo AS VALUES
    ( null   , 'xyz'  , 'pdq'  , null ),
    ( 'abc'  , 'def'  , 'ghj'  , null ),
    ( 'hsh'  , 'fff'  , 'oko'  , null );

So this would give results something like:

Col1 | 2
Col2 | 3
Col3 | 3
Col4 | 0

EDIT: to clarify, I know I can just use COUNT but I'm hoping for a way to loop over possibly a query to the system table first to avoid having to hand code 315 count statements. Thanks!

Something like 

FOR column_names IN SELECT * FROM information_schema.columns WHERE 
table_schema = 'public' AND table_name = 'vendor'
LOOP
 RAISE NOTICE 'doing %s', quote_ident(column_names.column_name);
 SELECT count(column_names.column_name) from vendor      
END LOOP;

Best Answer

Given this data:

create table t (Col1 text, Col2 text, Col3 text, Col4 text);
insert into t values
(null, 'xyz', 'pdq', null),
('abc', 'def', 'ghj', null),
('hsh', 'fff', 'oko',null);

You can use this block of code:

do
$$
declare
  cols text;

begin

    cols := string_agg('count(' || column_name::text || ') '  || column_name::text, ',')
    from (select column_name 
          from information_schema.columns
          where table_name = 't') c;

  execute format('create temp table counter as select %s from t;', cols);

end;
$$;

select * from counter;

✓

col1 | col2 | col3 | col4
---: | ---: | ---: | ---:
   2 |    3 |    3 |    0

db<>fiddle here

Related Question