Re: INSERT WHERE NOT EXISTS

On Wednesday 25 June 2003 03:37 pm, Mike Mascari wrote:
> Ian Barwick wrote:
> > On Wednesday 25 June 2003 20:06, Reuben D. Budiardja wrote:
<snip>
> >>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=3A4D611
>6.1A613402%40mascari.com&rnum=1&prev=/groups%3Fq%3DMike%2BMascari%2BINSERT%2
>BNOT%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. :-(

Interesting reading of the archive. In my particular case here, I don't have
to worry too much about the race thing. But the inherent problem is still
there.

Where can I read / learn more about the so-called "savepoints"? This is the
first time I've heard it. Granted, I've only been using postgresql recently.

Thanks.
RDB

--
Reuben D. Budiardja
Department of Physics and Astronomy
The University of Tennessee, Knoxville, TN