Updating geometry as Multipolygon from Polygon table using PostGIS? [closed]

Question

There are two tables in PostGIS. In which, one has polygon geometries with layer name wise and another one table has master layer name list.

The requirement is to collect the polygon geometries as layer wise from first table and update it as Multipolygon in the geometry column of second table that is layer master table. Below screenshot describes the requirement.

But boundaries of the each polygon should be displayed when viewing the second table on QGIS/Pgadmin graphical viewer after updating them as Multipolygon.

Seeking a sample PostGIS queries to achieve the above.

Answer 1

Try ST_COLLECT:

update master as m
set geom = w.geom
from
    (select st_collect(geom) geom, layer
     from wise
     group by layer) w
where m.layer = w.layer

If you want the borders of overlapping polygons to dissolve then use ST_UNION