2

I have a table with the following structure:

create table pairs (
   block text,
   left_entity int,
   right_entity int
);

That table was the result of a record linkage process. One of the particularities of the table is that left_entity < right_entity.

An example of the data inside the table is:

insert into pairs values 
( 'A' , 31 , 36 ),
( 'A' , 31 , 34 ),
( 'A' , 33 , 39 ),
( 'A' , 32 , 33 ),
( 'A' , 32 , 39 ),
( 'A' , 36 , 37 ),
( 'A' , 34 , 37 ),
( 'A' , 34 , 36 ),
( 'B' , 47 , 49 );

Every pair on this table is considered the same entity.

My goal is to aggregate in an array all the pairs that belong to the same entity. So, for the example above

|A| {31, 34, 36, 37} |
|A| {32, 33, 39 }    |
|B| {47,49}          |

etc

I think that the approach should be a recursive CTE or a LATERAL JOIN (a similar problem is here), but I can't figure out how to break the loop (since the CTE solution assumes a relationship parent-child) or how to code the function for the LATERAL JOIN (the example assumes that there is only one, but trying that function with this data returns several rows). (It seems that the problem is that the data has a graph like structure, instead of the hierarchical structure assumed in the examples).

Any help will be appreciated.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.