ccjmne ccjmne - 6 months ago 8
SQL Question

Swapping records' values for a column with a UNIQUE constraint in Postgre SQL

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
id
s 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
    name
    s 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
    id
    s 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
      id
      s 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
id
s
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

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');