merging layer stored in PostGIS

Question

I have a bunch of layers stored in PostGIS that I want to merge. I have spent quite some time investigating various approaches but I ket tripping over getting the GIDs to update properly.

In the end the only way that I found that worked was to:

1. load the layers into QGIS from PostGIS and then save them as shape files in a temp dir.
2. remove the layers from QGIS and reload them from the shape files.
3. delete the GID attribute from the layers and save the shape files again.
4. then use Vector->data managment->merge shape files to create a merged shape file
5. finally use SPIT to get the merged file back into PostGIS

The purpose of this 'question' is two fold, firstly to document something at works and secondly to ask if there is a better way of doing it.

Things that I discovered on this journey:

• you can not paste features into a PostGIS layer because these don't get the appropriate magic in the GID field to allocate a new number from the sequence. (this is arguably a bug in QGIS ??)
• Splitting feature in a PostGIS layer works as it should and the new feature picks up a GID when the layer is saved.

Answer 1

How about

CREATE TABLE tableName AS SELECT * FROM table1 UNION SELECT * FROM table2

Answer 2

As a GUI alternative to @zimmi 's answer. I was able to:

1. Load two diffrent tables from PostGIS both with gid.
2. Copy features from layer1 (table1) inside QGIS.
3. Activated the second layer2 (table2) for editing
4. Pasted the copied items from layer1 (table1) into layer2.
5. Saved the edits.

I went on to open the edited layer and the GID field updated correctly with no duplicate values.

SYSTEM: Windows XP, QGIS 1.9.0-Master (OSGeo4W); PostgreSQL 9.2.1 ,PostGIS 2.0