How simultaneously swapping unique IDs in Postgres

Hi friends

Here's a quick tip for when you're working with Postgres, this came in clutch for me when I had to use DEFERRABLE INITIALLY IMMEDIATE

I'm sharing this with a practical example where two rows in a table must exchange unique values simultaneously within the same transaction. This is common when swapping unique IDs or handling cyclic dependencies.

Example: Swapping Unique Teacher IDs

Consider a classes table where each class has a unique teacher_id, but you need to swap the teachers between two classes. The UNIQUE constraint would normally prevent this in a single UPDATE statement because, mid-operation, there would temporarily be duplicate teacher_id values.

So we can Define the table with a unique constraint that is DEFERRABLE INITIALLY IMMEDIATE:

CREATE TABLE classes (
    id INT PRIMARY KEY,
    teacher_id INT NOT NULL UNIQUE DEFERRABLE INITIALLY IMMEDIATE,
    class_name VARCHAR(100)
);
-- Insert initial data
INSERT INTO classes VALUES (1, 101, 'Biology'), (2, 102, 'Chemistry');

By default, the database checks the uniqueness after every statement.
Perform the swap within a transaction by explicitly deferring the constraint check:

BEGIN;
-- Temporarily set the specific constraint to DEFERRED mode for this transaction
SET CONSTRAINTS classes_teacher_id_key DEFERRED;

-- The update statements can now run without immediate failure
UPDATE classes SET teacher_id = 102 WHERE id = 1; -- Temporarily creates duplicate 102
UPDATE classes SET teacher_id = 101 WHERE id = 2; -- Resolves the duplicate

COMMIT;
-- At the COMMIT point, the database checks all deferred constraints.
-- Since the final state is valid (101 and 102 are unique again), the transaction succeeds.

If you tried these updates without the BEGIN; ... SET CONSTRAINTS DEFERRED; ... COMMIT; block, the first UPDATE statement would immediately fail due to the INITIALLY IMMEDIATE setting, which acts as a per-statement check.

this example demonstrate how to use DEFERRABLE INITIALLY IMMEDIATE constraints to swap unique values within a transaction:

Hope that helped!

Hash