I have two unique instances of Postgres using the same database schema but with independent datasets. One of the tables has a column setup for auto increment and it is a primary key. No other tables depend on this primary key.

Is there a way to merge these two databases together and have the import mechanism reassign new keys (the auto increment column) on this specific table only?

  • It sounds like simply inserting the data from database 2 into database 1 would be sufficient here; when it gets tricky is when the primary key is used as a foreign key by other tables.
    – RDFozz
    Aug 23, 2017 at 16:34
  • @RDFozz exactly. Since autokey starts at 1 and goes up, it's almost guaranteed that it will be in use and thus cause a primary key collision
    – spy
    Aug 23, 2017 at 16:36
  • 1
    so, you insert the columns except for the primary key, and allow the engine to assign the next autoincrement ID. From what you said, the key doesn't really matter, and the data in the two DBs is unique. There may well be s tool that can do this (there are such tools for MS SQL), but you don't need anything fancy: INSERT INTO DB1.TABLE (<all columns but PK>) SELECT (<all columns but PK>) FROM DB2.TABLE; or the equivalent should do it.
    – RDFozz
    Aug 23, 2017 at 16:51
  • I gotcha, however it's a large volume of data being exported via pg_dump and imported using psql. Modifying millions of records by hand isn't feasible
    – spy
    Aug 23, 2017 at 16:55
  • Stepping out - don't know enough about the tools in question to help. I simply envisioned a SQL interface with access to both databases, and literally running that query (no manual record by record changes required).
    – RDFozz
    Aug 23, 2017 at 17:02

2 Answers 2


What I would do is,

  1. Start a transaction.
  2. Update all constraints on the database you're merging in to ON UPDATE CASCADE
  3. Set all of the ids to be offset by the max(id) of the table you're merging into, with a comfortable margin (so you know they won't overlap at the time of merger)
  4. Merge them in.
  5. Restart the sequence at a number equal to or greater than the maxid of the table's column.


CREATE TABLE foo ( foo_id serial PRIMARY KEY );
CREATE TABLE bar ( foo_id int REFERENCES foo );
INSERT INTO bar (foo_id) VALUES (1);


    DROP CONSTRAINT bar_foo_id_fkey ,
    ADD CONSTRAINT bar_foo_id_fkey
      FOREIGN KEY (foo_id)
      REFERENCES foo(foo_id)
  UPDATE foo SET foo_id = foo_id + 1e6;

Now you've got,

TABLE bar;
(1 row)

Now when you load db2, set the max id to max(foo_id) on the seq.

SELECT setval('foo_foo_id_seq', (SELECT max(foo_id) FROM foo), true);
(1 row)

And, now when the next row is inserted, it calls nextval (under the hood), and gets

SELECT nextval('foo_foo_id_seq');
(1 row)

If you're doing this kind of a stuff a lot, look at using UUID's instead of integer sequences.


I found a few other related questions and answers


Another mechanism which was found on the postgres user mailing list was to insert the table without the auto id into a temporary table, then export the temp table. Then import the temp table into the other server. This was the approach that I ended up with.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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