4

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.

5

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
  • 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 at 13:29
3

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, privacy policy and cookie policy

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