how to get distinct values and their count for each column in postgresql

Question

I think my question is the same than this one, but it wasn't clear so the responses are not what I'm looking for.

I have a table built by osm2pgsql of 70 columns and about a million rows. Of the 70 columns, only 2 have values for each row (one is a geolocation with an index on it), the remaining ones form a sparse matrix.

I put in this fiddle a simplified version of what I have and what I want. A little explanation here : each value of each column got its count, and each column also got its count, with a NULL value to indicate that it's the column count.

What is the desired result for the given SQL FIDDLE data? – java Mar 16 '16 at 09:34 — java, Mar 16 '16 at 09:34
the one stored in expected_result! – Borbag Mar 16 '16 at 09:38 — Borbag, Mar 16 '16 at 09:38
@sagi got what is the wanted result, see his reponse – Borbag Mar 16 '16 at 09:43 — Borbag, Mar 16 '16 at 09:43

score 0 · Answer 1 · edited May 23 '17 at 11:59

I came with a partial response :

SELECT string_agg(
   'SELECT ' ||
       '''' || quote_ident(attname) || ''' as column_name, ' ||
       quote_ident(attname) || '::text as value_name, ' ||
   ' count(*) as value_count' ||
   ' FROM ' || attrelid::regclass ||
      -- ' WHERE ' || quote_ident(attname) || ' is not NULL' ||
   ' GROUP BY ' || quote_ident(attname) || ' ',
   'UNION ALL ')
FROM   pg_attribute
WHERE  attrelid = '<my_table_name>'::regclass
AND    attnum  >= 1           -- exclude tableoid & friends (neg. attnum)
AND    attisdropped is FALSE  -- exclude deleted columns
GROUP  BY attrelid;

Thanks to that thread. I still have to get the correct values when value_name is NULL, but I'm very close.

how to get distinct values and their count for each column in postgresql

1 Answers1