Question: How can I get the distinct values of all columns in a single table in Postgres?

Question

How can I get the distinct values of all columns in a single table in Postgres?

Answers 2
Added at 2016-12-13 22:12
Tags
Question

Ideally I'd like to run a single query that returns a table where each row is a column name of a specified table, and jsonb array of all the distinct values in the table corresponding to that column.

The tricky part seems to be doing so dynamically, where I can specify just the table and not each individual column.

I can retrieve all the column names of the relevant table from information_schema.columns, but is there an easy way to couple this with a query to retrieve all the distinct values for each column?

Answers
nr: #1 dodano: 2016-12-13 22:12

No sure I understood you completely, but this seems to do what you want:

select json_build_object(colname, array_agg(distinct val))
from the_table
  cross join lateral json_each_text(row_to_json(the_table)) as j(colname,val)
group by colname;
nr: #2 dodano: 2016-12-13 22:12
create table example(id int primary key, str text, val numeric);
insert into example values
(1, 'a', 1),
(2, 'a', 2),
(3, 'b', 2);

select key, array_agg(distinct value)
from example, jsonb_each_text(to_jsonb(example))
group by key;

 key | array_agg 
-----+-----------
 id  | {1,2,3}
 str | {a,b}
 val | {1,2}
(3 rows)    

or

select key, json_agg(distinct value)
from example, jsonb_each(to_jsonb(example))
group by key;

 key |  json_agg  
-----+------------
 id  | [1, 2, 3]
 str | ["a", "b"]
 val | [1, 2]
(3 rows)    
Source Show
◀ Wstecz