Swapping records' values for a column with a UNIQUE constraint in PostgreSQL

Question

Goal:

Swapping values in some table's records for a particular column that has a UNIQUE constraint.

---

Example:

Considering the following table:

CREATE TABLE records (id numeric, name text);
ALTER TABLE records ADD CONSTRAINT uniq UNIQUE (id);
INSERT INTO records VALUES
  (1, 'First record'),
  (3, 'Second record'),
  (2, 'Third record'),
  (4, 'Fourth record');

We get, from querying for SELECT id, name FROM records ORDER BY id;:

| id |          name |
|----|---------------|
|  1 |  First record |
|  2 |  Third record |
|  3 | Second record |
|  4 | Fourth record |

I need the ids for the Second record and Third record swapped. That is, I want the following output:

| id |          name |
|----|---------------|
|  1 |  First record |
|  2 | Second record | <-- this record previously had the id 3
|  3 |  Third record | <-- this record previously had the id 2
|  4 | Fourth record |

---

Obviously:

• I can't just swap the names for these records, my actual database is a tad bigger than this:
  • this table has more information stored and
  • its id column is actually a PRIMARY KEY that is referenced by other tables.
• I don't wanna go through the hassle of utilizing temporary ids that aren't assigned to any record yet to execute this swapping.
• The 'swapping' in question does not only involve two records, but an arbitrary long set of them.
• The records also don't technically get 'swapped' but simply reassigned, with the following properties:
  • each id will always be unique after each bulk reassignment operation and
  • most of the ids from a previous assignment will overlap with those of the new assignment map.

---

What I've tried:

I have tried swapping my data in a single query by generating a temporary reassignment table and using it in an UPDATE ... SET ... FROM ... WHERE query.

Here's my temporary reassignment table, which will swap the ids 2 and 3:

(VALUES
  (3, 2),
  (2, 3)
) AS swap(id, new_id)

And here's how I use it:

UPDATE records AS record SET
  id = swap.new_id
FROM (VALUES
  (3, 2),
  (2, 3)
) AS swap(id, new_id)
WHERE record.id = swap.id;

SELECT id, name FROM records ORDER BY id;

As you can see in this SQLFiddle link, it works pretty well... until you add the UNIQUE constraint on the id column.

---

How could I get this working under all the conditions listed above?

Answer 1

The issue is with the CONSTRAINT's definition.

---

PosgreSQL's CONSTRAINTS can be DEFERRABLE. From their documentation, we gather the following:

IMMEDIATE constraints are checked at the end of each statement. DEFERRED constraints are not checked until transaction commit. Each constraint has its own IMMEDIATE or DEFERRED mode.

In the case we're facing, the ids will still be UNIQUEs AFTER the transaction, while indeed that CONSTRAINT cannot be satisfied DURING the reassignment of ids.

Simply making the relevant CONSTRAINT DEFERRABLE will solve the problem at hand.

CREATE TABLE records (id numeric, name text);
ALTER TABLE records ADD CONSTRAINT uniq UNIQUE (id) DEFERRABLE INITIALLY IMMEDIATE;
INSERT INTO records VALUES --                           ^
  (1, 'First record'),     --                          HERE
  (3, 'Second record'),
  (2, 'Third record'),
  (4, 'Fourth record');