Reputation: 3
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
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