What I would do is,
- Start a transaction.
- Update all constraints on the database you're merging in to
ON UPDATE CASCADE
- 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)
- Merge them in.
- 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 );
INSERT INTO foo DEFAULT VALUES;
CREATE TABLE bar ( foo_id int REFERENCES foo );
INSERT INTO bar (foo_id) VALUES (1);
ALTER TABLE bar
DROP CONSTRAINT bar_foo_id_fkey ,
ADD CONSTRAINT bar_foo_id_fkey
FOREIGN KEY (foo_id)
ON UPDATE CASCADE;
UPDATE foo SET foo_id = foo_id + 1e6;
Now you've got,
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);
And, now when the next row is inserted, it calls
nextval (under the hood), and gets
If you're doing this kind of a stuff a lot, look at using UUID's instead of integer sequences.