• Items Managed
  • Transactions Per Hour
  • Issues solved

About

Command Prompt, Inc. is the oldest and largest dedicated PostgreSQL support provider in North America. Since 1997, we have been developing, supporting, deploying and advocating the use of the World's Most Advanced Open Source Database.

About Us

PostgreSQL Community

Point In Time Recovery Tools for PostgreSQL made easy.
Plphp can now be used to write functions in PostgreSQL.
SimpyCity is a mapping system for PostgreSQL stored procedures/queries.
Read More

Featured Clients

Become Our Client

Featured Article

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 ...

Read More