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

Question

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

Gordon Linoff · Accepted Answer · 2016-07-09 18:46:48Z

1

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;

answered Jul 9, 2016 at 18:46

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related