PostgreSQL - properly change ID of table row

Question

How to change id of some table's row?

Like:

UPDATE table SET id=10 WHERE id=5;

But in way that it would cascade changes to every other table that references this table with that id?

I want to do this, because I need to import data from another database which has most of the same tables, but ids are different. So if ids would match old database, it would be easier to import data correctly.

So if I want to change primary key I need to define all foreign keys that are referenced that primary key? — Andrius, Mar 07 '13 at 10:08
@oerp Yes. `ON UPDATE CASCADE` for **foreign key** means "update this field when refferenced key is updated" — Ihor Romanchenko, Mar 07 '13 at 10:17
Maybe there is some command to apply this 'ON UPDATE CASCADE' to every foreign key that references that primary key? Because there are a lot of tables that are referencing this primary key. So manually updating every table would take a lot:) — Andrius, Mar 07 '13 at 10:35

Clodoaldo Neto · Answer 1 · 2013-03-07T13:14:44.130

Suppose you have these two tables:

create table referenced (id integer primary key);
create table referencer (a integer references referenced (id));

Table referencer references table referenced:

=> \d referencer
  Table "public.referencer"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 
Foreign-key constraints:
    "referencer_a_fkey" FOREIGN KEY (a) REFERENCES referenced(id)

Then you insert a value in both:

insert into referenced values (1);
insert into referencer values (1);

select *
from
    referenced rd
    inner join
    referencer rr on rd.id = rr.a
;
 id | a 
----+---
  1 | 1

Now you want to change the reference to on update cascade:

alter table referencer
    drop constraint referencer_a_fkey,
    add foreign key (a) references referenced (id) on update cascade;

And update it:

update referenced set id = 2;

select *
from
    referenced rd
    inner join
    referencer rr on rd.id = rr.a
;
 id | a 
----+---
  2 | 2

Now you will have another problem in the referenced table primary key if the updated id already exists. But that would make another question.

UPDATE

This is dangerous so backup the db first. It must be done as superuser:

update pg_constraint
set confupdtype = 'c'
where conname in (
    select
        c.conname
    from
        pg_constraint c
        inner join
        pg_class referenced on referenced.oid = c.confrelid
    where
        referenced.relname = 'referenced'
        and
        c.contype = 'f'
);

It will change all the foreign key constraints on the referenced table to on update cascade

Thanks. I see you can do it for let say one particular table. But let say there are many tables that references that one primary key. Is there a way to automatically alter all tables that references that primary_key? Because there are more like one hundred tables that references this primary key — Andrius, Mar 07 '13 at 12:24
@ClodoaldoNeto, have you tried the update all approach yourself? Isn't there a need to restart PostgreSQL because of the `relcache`? (e.g. the old values could still be in memory, so it would not work right away) — MatheusOl, Mar 07 '13 at 15:43
@MatheusOl Tried only with the sample tables I posted above. — Clodoaldo Neto, Mar 07 '13 at 15:56

jnemecz · Answer 2 · 2013-03-07T10:48:45.017

3

You will need to change your foreign key and set ON UPDATE action to CASCADE. When you change a value, all associated values will be changed too.

This is an example how to define it:

CREATE TABLE order_items (
    product_no integer REFERENCES products ON UPDATE CASCADE,
    order_id integer REFERENCES orders ON UPDATE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

For more information see http://www.postgresql.org/docs/current/static/ddl-constraints.html

edited Mar 07 '13 at 10:48

answered Mar 07 '13 at 09:24

jnemecz

3,171
8
41
77

For example there is this column 'id' from table res_users: "res_users_pkey" PRIMARY KEY, btree (id) So does it mean it's not set 'ON UPDATE CASCADE'? If not, how to alter it to become like that? – Andrius Mar 07 '13 at 09:51
1

@oerp - you need to set this behaviour on another table's foreign key. I don't know a structure of your tables, but if you have a table `users` containing users accounts and another table e.g. `logins` containg login times referencing to table `users` via foreign key you need to set `ON UPDATE` action on this foreign key in table `logins`. – jnemecz Mar 07 '13 at 10:08
@user1315357 Please link to the `current` version of the malual. Like `http://www.postgresql.org/docs/current/static/ddl-constraints.html` (the word `current` instead of version number). – Ihor Romanchenko Mar 07 '13 at 10:15

PostgreSQL - properly change ID of table row

2 Answers2

UPDATE

Linked