Forum OpenACS Q&A: Re: PostgreSQL MVCC transaction model

Posted by Jeff Davis on
A really simple (and mostly wrongheaded) example is something like:
insert into tmp(pk1,val1) select :pk1, :val1
   where not exists (select 1 from tmp where pk1 = :pk1)
to ensure you don't throw an error if the primary key already exists (this is wrongheaded since with oracle at least, you should just attempt the insert and do an update if you get a pk error but you can't do that on postgres in a transaction).

By default on oracle this can actually generate a primary key violation errors even though it looks on the surface like it is "one statement".