How to adapt a PostGIS query to modify the input table instead of creating a new table?

Question

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;

Input polygon features: enter image description here

Output polygon features: enter image description here

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

• Inactivated Account: How are the output features and the source features related - by and ID or some sort?
• eclipsed_by_the_moon: I've uploaded two sceenshots to illustrate the question. The numbering of the polyongs doesn't matter as long as the values are unique.
• Inactivated Account: How would you handle deleting some polygons from the original table? I would be doing a drop and create with the same schema in order to accomplish this...

Answer 1

You cannot remove lines in the table you are currently selecting! But you can use your solution in "Edit 1" with a temporary table:

BEGIN;
CREATE TEMP TABLE temp1 ON COMMIT DROP AS
SELECT
 row_number() over() AS gid,
 sbqry.geom AS geom
FROM 
(SELECT
 (ST_Dump(ST_Union(geom))).geom::geometry(Polygon, /*SRID*/) AS geom
FROM public.input_table
) AS sbqry;
TRUNCATE public.input_table;
INSERT INTO public.input_table (gid, geom) SELECT gid, geom FROM temp1;
COMMIT;

Note: I haven't checked your part of the query for the select/union stuff...

• eclipsed_by_the_moon: Your query works just fine. Just for info: UPDATE and SET can't be used in this case?
• Elektordi: In SQL, the UPDATE action is for updating existing rows one by one, and merging rows is not considered as an update. If you really want to use UPDATE, you can update the "first" row of the future union to be the new union and then delete the other ones, the SQL code will be really more complicated, but it's possible.