Re: INSERT WHERE NOT EXISTS

Ian Barwick wrote:

> On Wednesday 25 June 2003 20:06, Reuben D. Budiardja wrote:
>
>>Hi,
>>I am developing application with PHP as the front end, PGSQL as the
>>backend. I am trying to figure out what's the best way to do this.
>>I want to check if an entry already exists in the table. If it does, then I
>>will do
>>UPDATE tablename ....
>>
>>otherwise, I will do
>>INSER INTO tablename...
>
> (...)
>
>
>>I vaguely remember in Oracle, there is something like this:
>>
>>INSERT INTO mytable
>>SELECT 'value1', 'value2'
>> FROM dummy_table
>> WHERE NOT EXISTS
>> (SELECT NULL FROM mytable
>> WHERE mycondition)
>>
>>This query will do INSERT, if there is not an entry already in the TABLE
>>mytable that match the condition mycondition. Otherwise, the INSERT just
>>fails and return 0 (without returning error), so I can check on that and do
>>update instead.
>
>
> This kind of query should work; just leave out the "FROM dummy_table" bit.
> (in Oracle it would be "FROM dual").

I proposed that same solution 3 years ago. Tom shoots it down:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=3A4D6116.1A613402%40mascari.com&rnum=1&prev=/groups%3Fq%3DMike%2BMascari%2BINSERT%2BNOT%2BEXISTS%26ie%3DUTF-8%26oe%3DUTF-8%26hl%3Den

Reuben must be prepared for unique key violation, I'm afraid. And,
despite the optimism in the link, we still don't have savepoints. :-(

Mike Mascari
mascarm(at)mascari(dot)com