Postgres deferred PRIMARY KEYS, a hidden gem
Oracle 7.3 supports it!
That is how this all started. A gentleman tweeted about a Postgres limitation that Oracle has not had since at least since Oracle 7.3.
The problem
As you can see in the tweet, Postgres by default will not defer a PRIMARY KEY check. Without the check being deferred the following will not work:
postgres=# select * from demo;
id
----
1
2
(2 rows)
postgres=# alter table demo add primary key(id);
ALTER TABLE
postgres=# begin;
BEGIN
postgres=# update demo set id=id-1;
UPDATE 2
postgres=# update demo set id=id+1;
ERROR: duplicate key value violates unique constraint "demo_pkey"
DETAIL: Key (id)=(1) already exists.
The solution
The solution, as mentioned ...