I have a table and I want to select all unique values of all attributes in one query. For example table Person. Which has 3 fields, name, age, city. Example:

And I want to have a result with unique values of every attribute

Is it possible to do this query?

I tried to make some quries with DISTINCT and UNION, but the result with always a multiplication of rows.

CodePudding user response：

This is not how relational databases work, but sometimes you got to do what you got to do.

You can do:

select a.name, b.age, c.city
from (select distinct name, row_number() over() as rn from t) a
full join (select distinct age, row_number() over() as rn from t) b on b.rn = a.rn
full join (select distinct city, row_number() over() as rn from t) c
  on c.rn = coalesce(a.rn, b.rn)

CodePudding user response：

One option is to aggregate into array, then unnest those arrays:

select x.*
from (
  select array_agg(distinct name) as names, 
         array_agg(distinct age) as ages,
         array_agg(distinct city) as cities
  from the_table
) d
  cross join lateral unnest(d.names, d.ages, d.cities) with ordinality as x(name, age, city);

I would expect this to be quite slow if you really have many distinct values ("millions"), but if you only expect very few distinct values ("hundreds" or "thousands") , then this might be OK.