0

Given a table like:

color animal place
orange bear California
blue bear Tokyo
green cat Florida
purple dog Florida
purple bird Oklahoma
red null Oklahoma

If any of the columns match, assume the rows should be combined

So, the first two rows are combined because the animal "bear": matched.

rows 3,4,5,6 are combined because the country Florida matched and the color purple matched and the state Oklahoma matched.

the final output should be:

colors animals places
{orange,blue} {bear} {California,Tokyo}
{green,purple,red} {cat,dog,bird} {Florida,Oklahoma}

(this is for entity resolution, they could have tons of ids in different systems and if any match I know that technically all match).

I have an idea for how to accomplish this by creating nested selects (or stacking views) and grouping by one column at a time but is there an easier way?

3
  • I would be surprised if there is an easy way to do this. Oct 27, 2023 at 4:37
  • You say you know a way. Please show it to help us help you. Also your version of Postgres and, ideally, a fiddle to work with. Oct 27, 2023 at 13:58
  • I edited to say I'm not so sure if I know how to do it... I started to do a group by on one column at a time then merge in values. that group by is made harder by the fact that there are null values too so I had to union in after Oct 27, 2023 at 16:44

1 Answer 1

0

I ended up finding my answer here: Grouping on any one of multiple columns in Postgres the three steps I had to do in addition were:

  • add an id to my table (which was actually a materialized view so didn't have one)
  • add indexes for performance since it was large
  • remove null values from the final array_aggregate

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.