Combinatorics in postgreSQL - selecting pairs

Question

I have a table below and I would like to select all unique pairs. It is difficult to phrase this problem without sounding like I just need to select distinct so I will write out the desired output and every possible combination.

Pair1: (1,4) and (2,5)

Pair2: (1,4) and (3,6)

Pair3: (2,5) and (3,6)

This is the same as the binomial coefficient:

n Choose r where n = 3 and k = 2.

Ideally the output will look something like:

I honestly don't know where to start with this one so please excuse that there is no first attempt.

Answer 1

Use self-join with conditions eliminating duplicates:

create table a_table (cola int, colb int);
insert into a_table values
(1, 4), (2, 5), (3, 6);

select * 
from a_table a
join a_table b 
on a.cola < b.cola and a.colb <> b.colb;

 cola | colb | cola | colb 
------+------+------+------
    1 |    4 |    2 |    5
    1 |    4 |    3 |    6
    2 |    5 |    3 |    6
(3 rows)

The above query works well if the column cola is unique. If the values of cola can be repeated, you should add an additional condition:

insert into a_table values (1, 8);

select * 
from a_table a
join a_table b 
on a.cola < b.cola and a.colb <> b.colb
or a.cola = b.cola and a.colb < b.colb
order by 1, 2;

 cola | colb | cola | colb 
------+------+------+------
    1 |    4 |    2 |    5
    1 |    4 |    3 |    6
    1 |    4 |    1 |    8
    1 |    8 |    2 |    5
    1 |    8 |    3 |    6
    2 |    5 |    3 |    6
(6 rows)