postgresql - Generate SQL to update primary key

Question

I want to change a primary key and all table rows which reference to this value.

# table master
master_id|name
===============
foo|bar

# table detail
detail_id|master_id|name
========================
1234|foo|blu

If I give a script or function

 table=master, value-old=foo, value-new=abc

I want to create a SQL snippet that executes updates on all tables which refere to table "master":

update detail set master_id=value-new where master_id=value-new;
.....

With the help of introspection, this should be possible.

I use postgres.

Update

The problem is, that there are many tables which have a foreign-key to the table "master". I want a way to automatically update all tables which have a foreign-key to master table.

Answer 1

The easiest way to deal with primary key changes - by far - is to ALTER your referring foreign key constraints to be ON UPDATE CASCADE.

You are then free to update the primary key values, and the changes will cascade to child tables. It can be a very slow process due to all the random I/O, but it will work.

You do need to watch out not to violate uniqueness constraints on the primary key column during the process.

A fiddlier but faster way is to add a new UNIQUE column for the new PK, populate it, add new columns to all the referring tables that point to the new PK, drop the old FK constraints and columns, then finally drop the old PK.