Re: Replaceing records

On Thu, 4 Sep 2003, Richard Ellis wrote:

> On Thu, Sep 04, 2003 at 12:17:35PM +0200, Csaba Nagy wrote:
> > [philosophical post regarding a missing feature of Postgres]
> >
> > I found there's no way to avoid failed inserts because of
> > unique constraint violations, causing automatic roll-back of the running
> > transaction.
> >
> > Now contention on insert has a quite high probability for this operation
> > in our application.
>
> Did you ever try this:
>
> insert into test (a, b, c, d)
>   (select 1, 2, 3, 4 where not exists
>     (select 1 from test where a=1 and b=2 and c=3 and d=4)
>   );
>
> If your table contains a=1, b=2, c=3, and d=4, nothing will happen, and
> there will be no failed transaction.  If your table does not contain a=1,
> b=2, c=3, and d=4, you'll get an insert of a row containing 1, 2, 3, 4.

Unfortunately that doesn't work if two transactions want to insert a row
containing 1,2,3,4 that are running concurrently.