There is ST_Node & ST_Polygonize, which should do what you need:

SELECT (ST_Dump(ST_Polygonize(ST_Node(noded_multi_geom)))).geom
FROM   (
  SELECT ST_Collect(<geom>) AS noded_multi_geom
  FROM   <your_table>
  -- WHERE <condition>
  -- GROUP BY <attribute>
) q
;

You might want to use a filter and/or grouping attribute to put a limit on the amount of edges to process.

There's no guarantee ST_Polygonize will create all polygons you want, or leaves out those you don't expect, so validate the result.

There is ST_Node & ST_Polygonize, which should do what you need:

SELECT (ST_Dump(ST_Polygonize(ST_Node(multi_geom)))).geom
FROM   (
  SELECT ST_Collect(<geom>) AS multi_geom
  FROM   <your_table>
  -- WHERE <condition>
  -- GROUP BY <attribute>
) q
;

You might want to use a filter and/or grouping attribute to put a limit on the amount of edges to process.

There's no guarantee ST_Polygonize will create all polygons you want, or leaves out those you don't expect, so validate the result.