Alexandr Vronskiy
Alexandr Vronskiy

Reputation: 3

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)?

Upvotes: 0

Views: 73

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270191

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;

Upvotes: 1

Related Questions