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 '17 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 '17 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 '17 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 '17 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 '17 at 17:02

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.

Sample

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

Now

BEGIN;
  ALTER TABLE bar
    DROP CONSTRAINT bar_foo_id_fkey ,
    ADD CONSTRAINT bar_foo_id_fkey
      FOREIGN KEY (foo_id)
      REFERENCES foo(foo_id)
      ON UPDATE CASCADE;
  UPDATE foo SET foo_id = foo_id + 1e6;
COMMIT;

Now you've got,

TABLE bar;
 foo_id  
---------
 1000001
(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);
 setval  
---------
 1000001
(1 row)

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

SELECT nextval('foo_foo_id_seq');
 nextval 
---------
 1000002
(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

https://stackoverflow.com/questions/7948234/combine-sql-databases-with-conflicting-ids

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

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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