Group by array overlapping
I don't care much about the cluster column just need ids properly aggregated.


In that case we can make use of the uniq and sort functions in the intarray extension:




with recursive a as (
  select id, array_agg(distinct clst) clst from w group by id)
, t(id,pid,clst) as (
  select id,id,clst from a
  union all
  select t.id,a.id,t.clst|a.clst
  from t join a on a.id&lt;&gt;t.pid and t.clst&amp;&amp;a.clst and not t.clst@&gt;a.clst)
, d as (
  select distinct on(id) id, clst from t order by id, cardinality(clst) desc)
select array_agg(id), clst from d group by clst;



array_agg | clst   
:-------- | :------
{6}       | {5}    
{2,3,4}   | {1,2,3}
{1,5}     | {0,4}  



db&lt;>fiddle here

Bear in mind that this is unlikely to perform well on millions of rows.