Query to get counts of values per column 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;
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