Alexandr Vronskiy Alexandr Vronskiy - 5 months ago 17
SQL Question

How output combinations values in column by rows in big table postgresql?

I have some big (121 million rows) table (id bigint, link_to bigint):

id | link_to
---|--------
1 | 4
2 | 4
3 | 4
5 | 7
6 | 7


And I need linking cross-self ID values by link_to values.
So it should be all combination between ID values (1,2,3) with same link_to value (4) and repeat for all link_to values.

In result should be:

id | link_to
---|--------
1 | 2
1 | 3
2 | 1
2 | 3
3 | 1
3 | 2
5 | 6
6 | 5


This select I will insert (with ON CONFLICT DO NOTHING for avoid dublicate unique index (id, link_to)) in same table.
I tried play with GROUP BY link_to -> array_agg(id) -> unnest -> WITH ORDINALITY, but without success results...

Any other solutions (CTE, window function, custom functions)?

Answer

You seem to be looking for a self-join:

select b1.id, b2.id
from bigtable b1 join
     bigtable b2
     on b1.link_to = b2.link_to and b1.id <> b2.id;