On 29th of July ( I know, there is a long backlog in waiting for 8.5 series. I'm working on it :), Tom Lane committed patch by Dean Rasheed:
Support deferrable uniqueness constraints. The current implementation fires an AFTER ROW trigger for each tuple that looks like it might be non-unique according to the index contents at the time of insertion. This works well as long as there aren't many conflicts, but won't scale to massive unique-key reassignments. Improving that case is a TODO item. Dean Rasheed
If you don't get why it's cool, let's see this example in any previous version:
# CREATE TABLE test ( i INT4 PRIMARY KEY ); # INSERT INTO test (i) VALUES (1), (2), (3); # SELECT * FROM test; i --- 1 2 3 (3 ROWS)
and when you try to update:
# UPDATE test SET i = i + 2; ERROR: duplicate KEY VALUE violates UNIQUE CONSTRAINT "test_pkey"
It fails because it checks the unique after every row update. It is still possible to do this update with some kind of loop that will order the rows in descending order over i, but it's not trivial.
In 8.5 the same construction generates error as well:
# UPDATE test SET i = i + 2; ERROR: duplicate KEY VALUE violates UNIQUE CONSTRAINT "test_pkey" DETAIL: KEY (i)=(3) already EXISTS.
But if I create table like this:
# CREATE TABLE test ( i INT4 PRIMARY KEY DEFERRABLE INITIALLY DEFERRED ); # INSERT INTO test (i) VALUES (1), (2), (3); # SELECT * FROM test; i --- 1 2 3 (3 ROWS)
You actually can update the values of i:
# UPDATE test SET i = i + 1; UPDATE 3 # SELECT * FROM test; i --- 2 3 4 (3 ROWS)
There still are some issues with this code – unclear error message when we try to set constraint as deffered when it was defined without “DEFERRABLE", some clarification issues with “DEFERRABLE INITIALLY IMMEDIATE" but it's definitely a cool feature.
Also – please note that it applies to constraints, not indexes!.
So, while you can have:
ALTER TABLE test ADD UNIQUE (i) DEFERRABLE INITIALLY DEFERRED;
CREATE UNIQUE INDEX q ON test (i) DEFERRABLE INITIALLY DEFERRED;
Which means that you're out of luck if you'd like partial, functional, unique index to be deferred. But I think we will get there. Maybe even in 8.5 – who knows 🙂