I'm in the process of merging databases with identical schemas. This will cause primary key collisions which one generally wants to avoid.
So there are a few things you have to do:
Run sql commands from
sudo -u postgres psql db_name
1. make absolutely sure that any foreign keys have the
update cascade property
\d table_name # Note the modifiers for the primary key: nextval('table_name_id_seq')... # Foreign-key constraints: ... ON UPDATE CASCADE ...
2. update the primary key
On the first database (other databases will get merged here), get the highest id from the table of interest.
select max(id) from table_name; max ------- 79157
We'll round up to
Now on the second database update the
id and also update the
sequence so that any new records are sufficiently high. Use the same sequence name that you noted from the
\d command earlier.
begin; update table_name set id = 100000+id; select setval('table_name_id_seq', 1+(select max(id) from table_name), True); commit;
Note, if you have more than 100,000 records in your table, then you may have to do a two stage adjustment.
-- note that the number is 1,000,000 begin; update table_name set id = id+1000000; update table_name set id = id-900000; select setval('table_name_id_seq', 1+(select max(id) from table_name), True); commit;