UNIQUE Constraints in PostgreSQL are not Transactional

I’ve recently encountered an unexpected “problem” with UNIQUE constraints in PostgreSQL. I’m stunned that I never noticed this before, since I’ve been working with PostgreSQL for many years. Either way, I guess I’m not the only one who doesn’t know about it. So, here it is …

Imagine you have the following table in your database:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
test=# \d employees
                  Table "public.employees"
   Column   |  Type   |              Modifiers
------------+---------+-------------------------------------
 id         | uuid    | not null default uuid_generate_v4()
 ordinality | integer | not null
Indexes:
    "employees_pkey" PRIMARY KEY, btree (id)
    "employees_ordinality_key" UNIQUE CONSTRAINT, btree (ordinality)

test=# SELECT * FROM employees;
                  id                  | ordinality
--------------------------------------+------------
 dbcecae2-a879-4f8b-8ba9-79a6821f013e |          1
 e2162ccf-d81c-4008-a46f-48640f3f9428 |          2
(2 rows)

Obviously, the UNIQUE constraint for the ordinality column is satisfied right now. Things start getting interesting when you attempt to update multiple rows within a single transaction:

1
2
3
4
5
BEGIN;
UPDATE employees SET ordinality = ordinality + 1;
COMMIT;
-- expected data after transaction:
-- two rows with ordinalities of 2 and 3.

After committing the transaction, the UNIQUE constraint should still be satisfied, right? Wrong! On my system, the UPDATE statement fails with the following error.

1
2
ERROR:  duplicate key value violates unique constraint "employees_ordinality_key"
DETAIL:  Key (ordinality)=(2) already exists.

PostgreSQL updates the table row by row and checks the UNIQUE constraint after changing each row. I would have expected the check to occur only once when committing the transaction, but that’s not how it works.

To achieve the intent of our transaction (i.e., incrementing oridinality by one for all rows), you have to rewrite it in a way that avoids collisions at all times. For instance, you could use two UPDATE statements:

1
2
3
4
BEGIN;
UPDATE employees SET ordinality = -ordinality;     -- negate
UPDATE employees SET ordinality = -ordinality + 1; -- negate and increment
COMMIT;

Ta-dah, problem solved!

Notes:
Software: PostgreSQL 9.5.3