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, is to use a DEFERRABLE PRIMARY KEY. A feature that Postgres has had since version 9.0 (7 years).

create table demo (id int primary key deferrable initially deferred);
 CREATE TABLE
postgres=# insert into demo values (1),(2);  
INSERT 0 2
 postgres=# select * from demo;
 id  
----
  1
  2
(2 rows)

postgres=# begin;
 BEGIN
 postgres=# update demo set id=id-1;  
UPDATE 2
 postgres=# update demo set id=id+1;
 UPDATE 2
 postgres=# commit;
 COMMIT
 

This is why Jim Mlodgenski's number one piece of advice for Oracle people is: Stop thinking like Oracle people; Postgres isn't Oracle. You are going to have to adjust your thinking on many things, from partitioning, hints, shared_buffers and redo logs. That doesn't mean Postgres can't handle your workload. It means you have to modify your application to work with Postgres, the way Postgres works.