Why & when you should use PostgreSQL deferred uniqueness constraints

When you want to ensure that a certain column in a table has unique values, what you usually do is create a UNIQUE constraint for the column (or e.g. for a pair of columns, when you want the uniqueness to apply to pairs of values in each tuple).

postgres=# CREATE TABLE numbers (number INTEGER, UNIQUE (number));
CREATE TABLE

postgres=# INSERT INTO numbers VALUES (0), (1), (2);
INSERT 0 3

Suppose that the number column represents an ordering, and you want all (or a subset of) tuples' index to be shifted by 1 to make way for a new item at a given index. Your first guess is probably this:

postgres=# UPDATE numbers SET number = number + 1;
ERROR:  duplicate key value violates unique constraint "numbers_number_key"
DETAIL:  Key (number)=(1) already exists.

Why did it fail? Because even though conceptually it would lead to the column's values of (1), (2), (3) - which would not violate the uniquenes constraing - the uniqueness constraint is checked immediately after updating each row. So, transitively, the column is briefly in a state of (1), (1), (2), which creates the error.

To make it work, we have to instruct the database to enforce this constraint at transaction commit time. Here's how to create the table with such setting default for this constraint:

postgres=# CREATE TABLE numbers (number INTEGER, UNIQUE (number) DEFERRABLE INITIALLY DEFERRED);
CREATE TABLE

postgres=# INSERT INTO numbers VALUES (0), (1), (2);
INSERT 0 3

postgres=# UPDATE numbers SET number = number + 1;
UPDATE 3

Clearly, it's working now. Read more on how to control the constraint's enforcing strategy on a per-transaction basis.