How to select distinct values on 2 columns in postgresql

Question

I have a table with col A and col B. Col A and Col B can have repetitive values. I want to select distinct values from Col A and Col B individually and populate them in 1 column as unique values. How do I do that?

Example

col_a | col_b
------+------
 1    | 3 
 2    | 4 
 3    | 5 
 4    | 7 
 5    | 8  
 6    |

I want to extract the total unique values in a table that says 1,2,3,4,5,6,7,8. How do I do that?

Should the result include or exclude the null value? (The col_b value from the row where col_a = 6.) — jarlh
– jarlh, Commented Sep 18, 2018 at 6:58

user330315 · Accepted Answer · 2018-09-18 06:25:34Z

6

You can use a UNION to combine two results with each column. A UNION will remove duplicates automatically:

select col_a as value
from the_table
union
select col_b 
from the_table;

answered Sep 18, 2018 at 6:25

user330315

Sign up to request clarification or add additional context in comments.

Comments

Tim Biegeleisen · Accepted Answer · 2018-09-18 05:09:12Z

2

One simple approach is to use a union:

SELECT DISTINCT val
FROM
(
    SELECT A AS val FROM yourTable
    UNION ALL
    SELECT B FROM yourTable
) t;

Demo

answered Sep 18, 2018 at 5:09

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

2 Comments

S-Man Over a year ago

If you would use UNION instead of UNION ALL you wouldn't need the DISTINCT.

Tim Biegeleisen Over a year ago

@S-Man You're right, but someone else already beat me to this better answer.

Collectives™ on Stack Overflow

How to select distinct values on 2 columns in postgresql

2 Answers 2

Comments

Demo

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related