1

I have a table within PostgreSQL/PostGIS that contains all point data. I need to add another large dataset to this existing table without overwriting the table. When I go into DB Manager within QGIS and try to import the new information I keep getting an error saying that the table I'm trying to write to already exists.

How can I add more information to this table without over writing the existing information already in the table? I wish to be able to do this with QGIS because it does all of the GEOM information for me.

DPSSpatial_BoycottingGISSE
  • 18,790
  • 4
  • 66
  • 110
jmoore3274
  • 145
  • 7

1 Answers1

1

If you have PostGIS, then you have PostgreSQL and I'm then guessing you have PGAdmin - you can likely do this in DBManager, but using PGAdmin is a good practice to develop...

Using PGAdmin (or DBManager) you'll have to write the SQL to join the two tables first in the way you want them to appear in the final table.

My best advice would be to write the SQL that joins the two tables together on the columns unique to both tables, taking all the columns (schema) from both tables and create a new table that will be a 'dev' version of your ultimate table.

CREATE TABLE tempTableName as  (SQL to do the join on the two tables goes here)

Then have a look at that table in QGIS to ensure it has all the schema you're looking for.

Now, unless someone has a different idea, after everything looks good in your 'temp / dev' table, you will:

DROP TABLE originalTableName

Then take the same SQL you wrote above and switch the name of the table to match the name of the original table:

CREATE TABLE originalTableName as (SQL you created to merge tables together)

(Note if you have spatial indices, primary keys, you should add the SQL to re-create them too)

What you should end up with after you've run the CREATE TABLE statement is all the schema (columns) from your original table (so processes already using it won't break) plus the columns from the table you joined.

We do this and it works just fine - but I think there is a way to update a table to add columns based on joins but I haven't had to use it yet...maybe someone will provide a different method...

DPSSpatial_BoycottingGISSE
  • 18,790
  • 4
  • 66
  • 110
  • I forgot how I did it before but I figured it out. So for those who want to be able to merge databases together into one data set using QGIS its pretty simple. First add two seperate PostGIS layers into QGIS. On the layer containing the information you wish to add to the database just select all the features on the layer then use the "Copy Features". Then on the other PostGIS layer you wish to add this information to enable editing then past the feature information then save the layer. QGIS will now update the database with the new information that you pasted. – jmoore3274 Jan 16 '16 at 17:33
  • oh you're talking about copying features into a similar table rather than adding columns ... you're right then, that's the GUI method in QGIS - you can use the same method I described above and use an INSERT statement in PGAdmin... – DPSSpatial_BoycottingGISSE Jan 16 '16 at 18:28