Christopher Kings-Lynne wrote:
> > If you don't have a primary key already, create a unique index on the
> > combination you want to be unique. Then:
> >
> > . Try to insert the record
> > . If you get a duplicate key error
> > then do update instead
> >
> > No possibilities of duplicate records due to race conditions. If two
> people
> > try to insert/update at the same time you'll only get one of the two
> results,
> > but that's the downside of the general approach you've taken. It's a tad
> > inefficient if the usual case is updates, but certainly not less efficient
> > than doing table locks.
>
> The idea was to stop our postgres logs being spammed up with unique
> constraint violation warnings....in which case your solution above is
> identical to our current one. Update and if it fails, insert, except since
> the row is likely to already be there - our current way will be a bit more
> efficient.
To control the spamming, use server_min_messages before the INSERT.
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073