6

I have the following query: 

select col1, col2
from ...
where...

which gives:

col1  col2
5
       17
4       5
12
5
       20
4      17
2       3

I want to convert it to one column with no duplicates as follows:

col3
5
17
4
12
20
2
3

How can I do that? I read this topic Combine two columns and add into one new column but it's not what I need... operator || won't help here.

Edit: col3 is simply a list of all numbers that appear in col2 and col1.

avi
  • 1,626
  • 3
  • 27
  • 45
  • third row has 4, not 5 - why? is there a rule to use first column if both specified?.. try `select coalesce(col1,col2) from table` - it will use col1, and if col1 is null, then col2 – Vao Tsun Aug 15 '17 at 09:56
  • @VaoTsun The logic is show me all numbers that appear in any of the columns but with no duplicates. col3 is simply a list of all numbers that appear in col2 and col1. – avi Aug 15 '17 at 10:02

4 Answers4

8

It seems you need union

select col1 as col3 from t where col1 is not null
union
select col2 as col3 from t where col2 is not null
Oto Shavadze
  • 40,603
  • 55
  • 152
  • 236
4
select coalesce(col1,col2) 
from table

it will use col1, and if col1 is null, then col2

https://www.postgresql.org/docs/current/static/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL

The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. It is often used to substitute a default value for null values when data is retrieved for display

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • coalesce is not enough... I edited my example to show why. The number 3 will never appear in the final result using coalesce. – avi Aug 15 '17 at 10:00
  • in your sample between 20.....2, 3 are missing 4, 17 - is it because you want distinct values only? I answered thinkng you want to **combine** two columns, not just select both columns as one – Vao Tsun Aug 15 '17 at 10:11
1

You can use the COALESCE function as documented at https://www.postgresql.org/docs/current/static/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL ; it returns the first of its arguments that is not null:

yesql# select col1, col2, coalesce(col1, col2) from foo;
 col1 │ col2 │ coalesce 
══════╪══════╪══════════
    5 │    ¤ │        5
    ¤ │   17 │       17
    4 │    5 │        4
   12 │    ¤ │       12
    5 │    ¤ │        5
    ¤ │   20 │       20
    4 │   17 │        4
(7 rows)
Dimitri Fontaine
  • 236
  • 1
  • 6
0

col3 is simply a list of all numbers that appear in col2 and col1.

In that case, this might be what you are looking for:

SELECT col1
FROM ...
WHERE ...
UNION
SELECT col2
FROM ...
WHERE ...
Ralf Stubner
  • 26,263
  • 3
  • 40
  • 75