Re: INSERT WHERE NOT EXISTS

From: "Reuben D(dot) Budiardja" <techlist(at)voyager(dot)phys(dot)utk(dot)edu>
To: Mike Mascari <mascarm(at)mascari(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: INSERT WHERE NOT EXISTS
Date: 2003-06-25 20:05:15
Message-ID: 200306251605.15610.techlist@voyager.phys.utk.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruce Momjian 2003-06-25 20:08:22 Re: Inherits tables and current CVS
Previous Message nolan 2003-06-25 20:02:47 Re: [GENERAL] Physical Database Configuration