Query to get counts of values per column

Question

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;

Answer 1

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

Answer 2

You can get the first part of that done easily like this,

SELECT FORMAT(
        E'SELECT %s\nFROM %I.%I.%I;' -- query template
        , string_agg(  -- generate the select list for query template
                FORMAT('count(DISTINCT %I) AS %I', column_name, column_name)
                , E',\n\t'
        ),
        table_catalog, -- not strictly required, but future safe
        table_schema,
        table_name
)
FROM information_schema.columns
WHERE table_name = 'foo'
GROUP BY table_catalog, table_schema, table_name; 

This will return a query like this,

SELECT count(DISTINCT column1) AS column1,
        count(DISTINCT column2) AS column2,
        count(DISTINCT column3) AS column3,
        count(DISTINCT column4) AS column4
FROM ecarroll.public.foo;

Which is pretty much what you want, except you need to pivot it.

 column1 | column2 | column3 | column4 
---------+---------+---------+---------
       2 |       3 |       3 |       0

To do that pivot, we can use unnest(ARRAY[cols]) AS col_name, so we essentially have to generate

• dynamic SQL to do the count()
• wrap that with more dynamic sql to do the pivot.

Like this,

SELECT FORMAT(
        $$
        SELECT ordinality AS column_number, distinct_values -- the col#, and count
        FROM (
                SELECT %s      -- This was the query we
                FROM %I.%I.%I  -- used previously
        ) AS t
        CROSS JOIN unnest(ARRAY[%s]) WITH ORDINALITY -- Here we use unnest(array)
                AS distinct_values;                  -- to pivot the table
        $$,
        string_agg(
                FORMAT('count(DISTINCT %I) AS %I', column_name, column_name)
                , E',\n\t'
        ),
        table_catalog,
        table_schema,
        table_name,
        string_agg(column_name, ', ')
)
FROM information_schema.columns
WHERE table_name = 'foo'
GROUP BY table_catalog, table_schema, table_name;

That returns a query like this..

    SELECT ordinality AS column_number, distinct_values
    FROM (
            SELECT count(DISTINCT column1) AS column1,
    count(DISTINCT column2) AS column2,
    count(DISTINCT column3) AS column3,
    count(DISTINCT column4) AS column4
            FROM ecarroll.public.foo
    ) AS t
    CROSS JOIN unnest(ARRAY[column1, column2, column3, column4]) WITH ORDINALITY
            AS distinct_values;

And you can just run \gexec and you'll get,

 column_number | distinct_values 
---------------+-----------------
             1 |               2
             2 |               3
             3 |               3
             4 |               0

Answer 3

If you are ok with statistics instead of exact numbers, you can use the pg_stats system view.

SELECT attname, null_frac, n_distinct FROM pg_stats WHERE tablename = 'yourtable';

If you join with pg_class, you can get approximate row counts too.