I'm trying to insert rows from one cartodb table into another using the method outlined here:

INSERT INTO gfw_mining_test (the_geom, the_geom_webmercator, objectid, country,type, gfwid, st_area_sh, st_length_) SELECT the_geom, the_geom_webmercator, objectid, country, type, gfwid, st_area_sh, st_length_ FROM gab_mining_staging WHERE cartodb_id >= 0 and cartodb_id < 500000

I'm getting an error that there's duplicate data in the table:

duplicate key value violates unique constraint "gfw_mining_test_pkey"

I've queried the table to get the primary key and it seems to be cartodb_id. Both my source and my target datasets have this column. I'm trying to append data from my source without including the cartodb_id column in the hopes that the cartodb_id values for these new rows will be automatically generated in the target table.

Does this make sense? Why am I getting this error? Is there another key constraint that I'm running up against?


I've come across this issue as well, and have come up with a work-around which is as follows:

  • Duplicate table
  • Rename/Delete Original table (This will, of course, break any sharing this table had)
  • Rename Duplicated table to have original table name
  • Insert data to the Duplicated table

It seems like it might have to do with when the original table, and constraints, were created: https://github.com/CartoDB/cartodb/issues/5612

| improve this answer | |
  • Great. Thanks for your answer on this-- nice to have someone confirm the issues I'm experiencing. Will follow this approach from now on. – Charlie Lefrak May 17 '16 at 13:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.