Merge Multiple Tables into a New Table in PostGIS

I am looking to merge a number of individual tables into a new table in PostGIS. This is an easy task when working with Shapefile data, but I am unsure how to do this in PostGIS. Any help would be greatly appreciated. I think I use Append, but I am unsure even where to start.

Answer

(Pre-flight-check: are attributes identical in all original tables? Is the geometry type exactly the same in all tables?)

You can either

1. create the (empty) table first, then use INSERT INTO...SELECT... FROM to get all the data from each of the original tables into the merged one.


2. Create the new table from one big UNION statement.

For 1 it might go:

CREATE TABLE merged (id serial primary key, attrib1 integer, attrib2 varchar(15),....);
SELECT AddGeometryColumn('merged','geom',,','XY');

INSERT INTO merged (attrib1, attrib2, ...., geom) SELECT attribA, attribB,...,geom FROM table_1;
INSERT INTO merged (attrib1, attrib2, ...., geom) SELECT attribA, attribB,...,geom FROM table_2;

and so on...

For option 2:

CREATE TABLE merged AS( 
SELECT attribA, attribB,...,geom FROM table_1
UNION 
SELECT attribA, attribB,...,geom FROM table_2
UNION

.... 
);
SELECT Populate_Geometry_Columns('merged'::regclass);

HTH, Micha