[GIS] How to merge two geometry columns of different tables to a single column in another table

geometrypostgispostgresql

I have many tables with geometry attribute which is saved in Postgis database. These tables are basically from more than one shapefiles which is stored in Postgis using geotools.

Now I want a common table to handle all the geom attributes in the table, as shown in the below example

For eg:

enter image description here

Is there a way to do this using PostGIS? Initially the Table geom_all with geom column contains NULL value. I tried updating the values using JOINS with the below query example. 

 UPDATE Geom_all g SET geom = (SELECT geom FROM Table-1 t WHERE g.id = t.id);

But this overrides with every new value i update in the geom column and also the sub query should return only 1 row, which in my case is not. I am not sure whether I can achieve this task using JOINS, it would be helpful to know if there is any other way to solve this.

NOTE: All the geom column contains geometry_type as MULTIPOLYGON and ST_SRID as 4326.

Best Answer

If I understand correctly, you want to merge the rows from multiple tables. You don't need to alter the geometry

Create table geom_all as
Select ID, geom from table1
UNION
select ID, geom from table2
UNION 
select ID, geom from table 3;

or if the table already exists:

Insert into geom_all (select ID, geom from table1);
Related Question