postgresql - Generate SQL to update primary key -

i want change primary key , table rows reference value.

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

if give script or function

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

i want create sql snippet executes updates on tables refere table "master":

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

with of introspection, should possible.

i use postgres.

the easiest way deal primary key changes - far - alter referring foreign key constraints on update cascade.

you free update primary key values, , changes cascade child tables. can slow process due random i/o, work.

you need watch out not violate uniqueness constraints on primary key column during process.

a fiddlier faster way add new unique column new pk, populate it, add new columns referring tables point new pk, drop old fk constraints , columns, drop old pk.