I use the following SQL query to create a table that contains dissovled polygons of an existing table:
-- Schema: public
-- Table: t_union
-- Input table: input_table
CREATE TABLE public.t_union AS SELECT
row_number() over() AS gid,
sbqry.geom
FROM
(SELECT
(ST_Dump(ST_Union(geom))).geom::geometry(Polygon, /*SRID*/) AS geom
FROM public.input_table
) AS sbqry;
Is there a way to modify the polygons of the input table instead of creating a new table?
Edit 1:
Meanwhile, I've created a SQL query to modify the input table:
INSERT INTO public.input_table
SELECT
row_number() over() + 100000 AS gid,
sbqry.geom
FROM
(SELECT
(ST_Dump(ST_Union(geom))).geom::geometry(Polygon, /*SRID*/) AS geom
FROM public.input_table
) AS sbqry;
DELETE FROM public.input_table WHERE gid < 100000;
UPDATE public.input_table SET gid = gid - 100000;
row_number() over() + 100000 AS gid
is a clumsy workaround to ignore the existing gid values and I don't know how to delete the input geometries in a simple way.
EDIT 2:
Perhaps this can be solved by using UPDATE
and SET
:
UPDATE public.input_table SET (geom) =
(SELECT COALESCE
((ST_Dump(ST_Union(geom))).geom::geometry(Polygon, /*SRID*/))
FROM public.input_table);
Unfortunately, an error occurs when executing the query:
set-valued function called in context that cannot accept a set