11

I can't seem to find a way to achieve this transactionally (or not)

What I need to achieve is non-standard, hence my difficulty finding a solution.

I need to code a data migration tool to "swap" old records with new records in a table, but I have the following requirements / constraints:

  • Don't lose the old records
  • Make all references to old records point to new records (that's not just db foreign keys, it's references out of my control in external services, caches, emails, historic data, bookmarks, you name it)
  • The migration code needs to be schema agnostic, i.e. shall not need to be updated if a new column is added to the table and independent of which other tables reference it.
  • I can't lock the table for more than the acceptable amount of time it takes to update two records.

So my ideal solution is, well, brute swap their primary keys...

Question is how can I swap the primary keys between two records in postgresql. I am having difficulty finding an approach that does not fail with duplicate key exception, i.e. an approach that runs the "validation" for the update transactionally.

I have tried

UPDATE table
SET id = (CASE id WHEN 1 THEN 2 WHEN 2 THEN 1 ELSE id END)

UPDATE table
SET id = CASE id WHEN 1 THEN 2 WHEN 2 THEN 1 END
WHERE id IN (1, 2);

Both failing on duplicate key constraint

I am using PostgreSQL 11.6

Paul White
  • 94,921
  • 30
  • 437
  • 687
Pedro Borges
  • 211
  • 2
  • 7

3 Answers3

8

You could use a deferred constraint. For that you need to drop and re-create the primary key:

CREATE UNIQUE INDEX mytable_primkey ON mytable (id);
ALTER TABLE mytable DROP CONSTRAINT mytable_pkey;
ALTER TABLE mytable ADD PRIMARY KEY USING INDEX mytable_primkey
   DEFERRABLE INITIALLY DEFERRED;

The update itself could then be done like that:

UPDATE mytable SET id = 3 - id WHERE id IN (1, 2);

Here 1 and 2 are used as examples, you can do that with any numbers.

If you cannot afford the down time required for adding a deferred primary key, you could do it with one more update like this;

BEGIN;
UPDATE mytable SET id = 0 WHERE id = 1;
UPDATE mytable SET id = 1 WHERE id = 2;
UPDATE mytable SET id = 2 WHERE id = 0;
COMMIT;

Here 0 is an arbitrary value that is not used as a value for id.

Laurenz Albe
  • 61,070
  • 4
  • 55
  • 90
5

The only way that I know this is possible is with a deferred constraint.

You will need to drop the primary key,

alter table x drop constraint x_pkey;

and add it again as deferrable:

alter table x add primary key (id) deferrable initially immediate;

Before performing the update you can defer the constraint:

set constraints x_pkey deferred;

and then perform the update and commit.

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=469e5168c84bdbb8361426d4458bcf88

Of course, if you have other tables referring to this table you will need to drop and recreate the foreign keys too.

Colin 't Hart
  • 9,455
  • 15
  • 36
  • 44
2

Solution for bulk updates for numeric primary keys, thanks to input from the authors of other answers:

-- disable foreign key constraint validation
BEGIN;
SET session_replication_role='replica';

-- update the pairs of ids to their negative counterparts
WITH query AS ('query to get pairs of ids to swap')
UPDATE table 
SET id = -id 
WHERE id in (query.id1, query.id2);


-- update the pairs of negated ids to their positive counterparts swapped
WITH query AS ('query to get pairs of ids to swap')
UPDATE table 
SET id = CASE id WHEN query.id1 THEN -query.id2
                 WHEN query.id2 THEN -query.id1
                 END
WHERE id in (query.id1, query.id2);


-- enable foreign key constraint validation
SET session_replication_role='origin';
COMMIT;
wiki answer
  • 1
  • 2