Question 90549 in PostgreSQL Question posted in
The official documentation can be found here.

Select all unique values of all attributes in one query – Postgresql

I have a table and I want to select all unique values of all attributes in one query.

For example table Person with 3 columns name, age, city.

Example:

Name age city
Alex 34 New York
Leo 34 London
Roy 20 London
Alex 28 Moscow
Mike 36 London

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

Name age city
Alex 20 New York
Leo 28 London
Roy 34 Moscow
36

Is it possible to do this query?

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

Tags:

2

Answers


  1. 0

    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)
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search