Postgres and using "EXCLUDE" for Deferred Unique Checks

I recently needed to create a conditional unique index for a hobby project that looks something like this;

CREATE UNIQUE INDEX t_idx0 ON t (y) WHERE y IS true;

The problem is that I am using an ORM where the order of operations is not able to be determined in advance. This means that an UPDATE and an INSERT might fire in the wrong order such that the unique index is violated.

Consider the following;

| x     | y   |
|-------|-----|
|Andrew |true |
|Martin |false|
|Uli    |false|

Now if the following are executed…

INSERT INTO t (x, y) VALUES ('Sampath', true);
UPDATE t SET y = false WHERE x = 'Andrew';

…then the end result of the transaction is acceptable, but the first statement will fail because in isolation of the other statement it will violate the unique index. The usual answer with referential-integrity checks is to use deferred checks. This means that the check is enforced at the end of the transaction rather than as each statement is executed. Unfortunately Postgres does not support deferred checks on indicies. Luckily however there is a way forward with the recently introduced EXCLUDE statement because EXCLUDE does support deferred enforcement. The setup of an EXCLUDE looks like this;

ALTER TABLE t ADD
EXCLUDE (y WITH =)
WHERE (y)
DEFERRABLE INITIALLY DEFERRED;

This can be read in natural language as “Don’t allow any rows where y is equal between rows only considering cases where y is true and don’t enforce this until the end of the transaction”. You can inspect the constraint when viewing the table with psql;

...
Indexes:
    "t_y_excl" EXCLUDE USING btree (y WITH =) WHERE (y) DEFERRABLE INITIALLY DEFERRED

To remove the constraint, use;

ALTER TABLE t DROP CONSTRAINT "t_y_excl";