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.
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
select coalesce(col1,col2)
from table
it will use col1, and if col1 is null, then col2
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
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)
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 ...
select coalesce(col1,col2) from table- it will use col1, and if col1 is null, then col2