Postgres - Regroup rows with at least one common attribute I have the following table :

Letter | Figure
-------|-------
   A   |    1
   B   |    1
   B   |    2
   C   |    2
   C   |    3
   D   |    3
   E   |    4


I would like to group rows that share a common letter OR figure. Result should then be :

Letter | Figure | Groupment
-------|--------|-------
   A   |    1   |   I
   B   |    1   |   I
   B   |    2   |   I
   C   |    2   |   I
   C   |    3   |   I
   D   |    3   |   I
   E   |    4   |   II


I know I can define a postgres function and re-scan the all table for each new row, but I'm trying to find something a little bit less unoptimized.

Thanks for your help !
This was tricky but fun. Using a recursive query:

with recursive
  v(l, f) as (
    -- select Letter, Figure from table
    values ('A', 1), ('B', 1), ('B', 2), ('C', 2), ('C', 3),  ('D', 3), ('E', 4)
    ),
  r as (
    select *
    from (
      select array[l] as l, array[l] as al, array[f] as f, array[f] as af, 1 as g, 1 as n
      from v order by l, f limit 1
      ) a
    union all
    select CASE WHEN g.newgrp THEN g.l ELSE r.l || g.l END, r.al || g.l,
           CASE WHEN g.newgrp THEN g.f ELSE r.f || g.f END, r.af || g.f,
           CASE WHEN g.newgrp THEN g + 1 ELSE g END, n+1
    from r
    join lateral (
      select CASE WHEN newgrp THEN ARRAY(SELECT l FROM v WHERE l&lt;&gt;ALL(r.al || g.l) AND f&lt;&gt;ALL(r.af || g.f) ORDER BY l, f LIMIT 1) ELSE l END AS l,
             CASE WHEN newgrp THEN ARRAY(SELECT f FROM v WHERE l&lt;&gt;ALL(r.al || g.l) AND f&lt;&gt;ALL(r.af || g.f) ORDER BY l, f LIMIT 1) ELSE f END AS f,
             newgrp
      from (
        select l, f, g.l = '{}' AND g.f = '{}' as newgrp
        from (
          select COALESCE(ARRAY(SELECT l FROM v WHERE f =ANY(r.f) AND l &lt;&gt;ALL(r.l)), '{}') as l,
                 COALESCE(ARRAY(SELECT f FROM v WHERE l =ANY(r.l) AND f &lt;&gt;ALL(r.f)), '{}') as f
          ) g
        ) g
      ) g on (true)
    where (not g.newgrp or (g.newgrp and g.l &lt;&gt; '{}'))
    ),
  g as (
    select distinct on (g) l, f, g
    from r
    order by g, n desc
    )
select v.l as Letter, v.f as Figure, g.g AS Groupment
from v
join g on (v.l =any(g.l) or v.f =any(g.f))
order by 3, 1, 2