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.