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)

);

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