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.