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

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?

Answer 1

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)