2

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;
  • what if in the third row, col1, had abc? – Evan Carroll Sep 27 at 0:37
3

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

  • thanks-- please see my edit above-- is there a way to do this dynamically without hard coding 315 column names? – user101289 Sep 26 at 20:59
4

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
0

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.

  • I've tried to use pg_class and query planner output to estimate counts on DISTINCT with poor results. I've run ANALYZE, but it didn't help. Is this just me? – Morris de Oryx Sep 27 at 3:51
  • @MorrisdeOryx can you share more details? It's statistics, so it's approximate by design. – filiprem Sep 30 at 10:30
  • I have one table with ~4M rows, 135K distinct values, and a distinct value estimate of under 3K rows. In other cases, the stats estimate is 100% correct, or plenty close. I've heard that PG 12 improves the options around stats on distinct values, but we're on 11.4 and I haven't tried PG 12 yet. – Morris de Oryx Sep 30 at 10:33
  • @MorrisdeOryx in this case Pg version is irrelevant. I just wonder what "poor results" did you get and how? – filiprem Sep 30 at 18:53
  • I looked back in my code and realized I'd have to provide an example to flesh out this question. You can find it here: dba.stackexchange.com/questions/249969/… I'd love a great way to get reasonably accurate distinct count estimates, if there is one. – Morris de Oryx Oct 1 at 0:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.