# 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. T…

## 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](<https://twitter.com/FranckPachot/status/888867529954820097>). 

## 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](<https://www.postgresql.org/docs/9.2/static/sql-set-constraints.html>). 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.

---
[View this page online](https://www.commandprompt.com/blog/postgres_deferred_primary_keys/)