4

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,436
  • 3
  • 22
  • 40
  • 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

6

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
  • 37,634
  • 51
  • 140
  • 215
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
  • 42,665
  • 8
  • 85
  • 115
  • 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
  • 201
  • 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
  • 25,305
  • 3
  • 37
  • 71