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

Postgresql – Find only distinct values from multiple columns in Postgres

Let us assume a Postgres table like this

CREATE TABLE example (
  value_1 VARCHAR(255),
  value_2 VARCHAR(255),
  value_3 VARCHAR(255),
  value_4 VARCHAR(255),
  value_5 VARCHAR(255)
);

Each row has a different value for each of the columns, and each row has the same values, but in different columns.

INSERT INTO
  example
VALUES
  ('a', 'b', 'c', 'd', 'e'),
  ('b', 'c', 'd', 'e', 'a'),
  ('c', 'd', 'e', 'a', 'b'),
  ('d', 'e', 'a', 'b', 'c'),
  ('e', 'a', 'b', 'c', 'd');

Yielding

# SELECT * FROM example;
 value_1 | value_2 | value_3 | value_4 | value_5 
---------+---------+---------+---------+---------
 a       | b       | c       | d       | e
 b       | c       | d       | e       | a
 c       | d       | e       | a       | b
 d       | e       | a       | b       | c
 e       | a       | b       | c       | d
(5 rows)

How can I select all the unique values between several columns spanning over multiple rows so that I get only one column list with distinct values 'a', 'b', 'c', 'd', 'e'? It is not known if each value exists in each column and the order of the values.

Tags:

3

Answers


  1. 0

    You have a table of related values, but in your query you don’t care about these relations; you only want to know which values exist in the table.

    One way to treat all the values the same is writing one query for each column and then union the results:

    select value_1 as value from mytable
union
select value_2 as value from mytable
union
select value_3 as value from mytable
union
select value_4 as value from mytable
union
select value_5 as value from mytable
order by value;
    Login or Signup to reply.
  2. 0

    Check the solution that uses unnest array function. But what about the leading/trailing spaces or data like these 'ab', '', 'c', 'd', 'e'?:

    select version();
-- drop table if exists example;

CREATE TABLE example (
  value_1 VARCHAR(255),
  value_2 VARCHAR(255),
  value_3 VARCHAR(255),
  value_4 VARCHAR(255),
  value_5 VARCHAR(255)
);

INSERT INTO example
VALUES
  ('a ', 'b', 'c', 'd', 'e'),
  ('b', 'c', 'd', 'e', 'a'),
  (' c', 'd', 'e', 'a', 'b'),
  ('d', ' e ', 'a', 'b', 'c'),
  ('e', 'a', 'b', 'c', 'd'),
  ('ab', '', 'c', 'd', 'e');

with
  t1 as (
    select
      row_number() over(order by 0) as id,
      unnest(array[value_1, value_2, value_3, value_4, value_5]) as val
    from example
  )
select distinct
  string_agg(val, '' ORDER BY val) as val
from t1
group by id
;

    Results:

       val   
---------
 ab cde
 a bcde
 abcde
 abcd e

    Try it on </>OneCompiler.

    Login or Signup to reply.
  3. 0

    After reading up on @Andrei Odegov’s unnest, it looks like that function can return multiple rows. To return the unique values from any row or column:

    select  distinct unnest(array[value_1, value_2, value_3, value_4, value_5])
from    example
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search