@ wrote... (1 year, 10 months ago)

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 psql.

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 100,000.

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;
Category: tech, Tags: database
Comments: 0
Click here to add a comment