2

I want to change the ID on two rows on Postgres, to switch them. They are already defined as foreign key so I cannot use a third number to do the switch.

How can I do this in one SQL query or transaction?

Example:

UPDATE mytable SET id=2 WHERE ID=1;
UPDATE mytable SET id=1 WHERE ID=2
sorin
  • 161,544
  • 178
  • 535
  • 806

3 Answers3

5

You mention foreign keys, but it remains unclear whether id is the referenced or the referencing column of a foreign key constraint.

If id is the referenced column you just define the fk constraint ON UPDATE CASCADE. Then you can change your id as much as you want. Changes are cascaded to the depending columns.

If id is the referencing column (and no other foreign key constraints point to it), then there is another, faster way since PostgreSQL 9.0. You can use a deferrable primary or unique key. Consider the following demo:

Note that you can't use this if you want to reference id with a foreign key constraint from another table. I quote the manual here:

The referenced columns must be the columns of a non-deferrable unique or primary key constraint in the referenced table.

Testbed:

CREATE TEMP TABLE t
( id integer
 ,txt text
 ,CONSTRAINT t_pkey PRIMARY KEY (id) DEFERRABLE INITIALLY DEFERRED
);
INSERT INTO t VALUES
 (1, 'one')
,(2, 'two');

Update:

UPDATE t
SET    id = t_old.id
FROM   t t_old
WHERE (t.id, t_old.id) IN ((1,2), (2,1));

Result:

SELECT * FROM t; 

id | txt
---+-----
2  | one
1  | two

You can also declare the constraint DEFERRABLE INITIALLY IMMEDIATE and use SET CONSTRAINTS ... DEFERRED in the same transaction.

Be sure to read about the details in the manual:

Even seems to work with DEFERRABLE INITIALLY IMMEDIATE and no SET CONSTRAINTS. I posted a question about that.

iLuvLogix
  • 5,920
  • 3
  • 26
  • 43
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    I understand that a referenced column can not be a column of a deferrable primary key. Or I'm wrong? – Clodoaldo Neto Apr 05 '12 at 16:31
  • 1
    As I commented previously in an answer that has been deleted, this wouldn't do because as of the latest PG9.1 you can't have a FOREIGN KEY pointing to a unique column with a DEFERRABLE constraint, and the question specifically mentions the existence of the FK. – Daniel Vérité Apr 05 '12 at 16:34
  • @Clodoaldo: Yes, that's an important restriction about foreign keys. I added a note to my answer.. – Erwin Brandstetter Apr 05 '12 at 16:39
  • @DanielVérité: It is unclear from the question whether `id` is the referenced or the referencing column of a foreign key constraint. If it is the referencing column, this would work. – Erwin Brandstetter Apr 05 '12 at 16:42
  • 1
    @Erwin: I wondered as well when reading the question, but since the title says "...ID [PK]...", I assumed mytable.ID is the primary key and some unmentioned table points to it. Maybe the author should clarify that. – Daniel Vérité Apr 05 '12 at 16:47
  • @DanielVérité: Yeah, clarification is in order. I split my answer in two cases. – Erwin Brandstetter Apr 05 '12 at 16:51
  • http://stackoverflow.com/a/12437451/905902 Funny, it appears I created almost the same example data for a similar question / topic. – wildplasser Sep 16 '12 at 14:29
  • @wildplasser: Small world. :) And fitting answer over there. – Erwin Brandstetter Sep 17 '12 at 10:46
1
begin;
alter table mytable drop constraint_name;
UPDATE mytable SET id=-1 WHERE ID=1;
UPDATE mytable SET id=1 WHERE ID=2;
UPDATE mytable SET id=2 WHERE ID=-1;
alter table mytable add table_constraint;
commit;
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • 1
    +1 for the simplicity, but only in case where the change is applied once... not suitable for a daily script to be run after any statement that modifies data – Victor Nov 20 '20 at 13:40
1

Have you tried something like:

BEGIN;

CREATE TEMP TABLE updates ON COMMIT DROP AS
SELECT column1::int oldid, column2::int newid
FROM ( VALUES (1, 2), (2, 1) ) foo;

UPDATE mytable
FROM updates
SET id = newid
WHERE id = oldid;

--COMMIT;
ROLLBACK;

Of course rollback gets commented out and commit in when you are ready to go.

scorpdaddy
  • 303
  • 3
  • 14