5

My postgresql column structure looks like this:

id  | from | to
---------------
1   |  A   |  B
2   |  A   |  B 
3   |  C   |  D

Now I want an result which looks like this:

 res 
-----
'A:B'
'B:A' 
'C:D'
'D:C'

where the first and the rows are permuted from A:B to B:A and 'C:D' to 'D:C' and the second column is omitted due to distinct operation.

2 Answers 2

6

This looks more like a stack overflow question yet Union will give a distinct combination.

SELECT concat("from",':',"to") as res From Table
UNION
SELECT concat("to",':',"from") From Table
1
  • You could also use the string concatenation operator - double pipe (||), i.e. SELECT depart || ':' || arrive FROM res UNION &c... - might (test) on be more efficient with big tables? On the other hand, [CONCAT](https://stackoverflow.com/a/11404025/470530) might be more portable?
    – Vérace
    Apr 1, 2019 at 13:29
4

You can also use least() and greatest() together with distinct. That would preserve the individual values without the need to concatenate them.

select distinct least("from", "to"), greatest("from", "to")
from the_table;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.