Re: Revisited: Transactions, insert unique. - Mailing list pgsql-general

From Ed Loehr
Subject Re: Revisited: Transactions, insert unique.
Date
Msg-id 39048915.E19A86F4@austin.rr.com
Whole thread Raw
In response to Re: Revisited: Transactions, insert unique.  (Joachim Achtzehnter <joachim@kraut.bc.ca>)
Responses Re: Revisited: Transactions, insert unique.
List pgsql-general
Joachim Achtzehnter wrote:
>
> Today, in a message to pgsql-general, David Boerwinkle wrote:
> >
> > it seems like this is something that ought to be handled
> > programmatically.  That is, query the table to see if the row exists,
> > then decide what you are going to do (insert or update) based on the
> > results of your query.
>
> It certainly 'can' be handled the way you describe, but to say that it
> 'ought' to be handled this way is going too far. It is common practice in
> database programming to simply try the most likely case and fall back to
> alternatives when an error is encountered. For example, if one expects 99%
> of inserts to be unique one may simply try the insert and when this fails
> because of a duplicate key error one can update instead. This is slightly
> more efficient than doing the extra query in 100% of cases.

More efficient, yes.  However, given the lack of "statement-only" aborts
and the state of pg error codes (all strings, no numeric codes), the
programmatic/query check appears not only compulsory but simpler code-wise.

Regards,
Ed Loehr

pgsql-general by date:

Previous
From: gme@ufba.br
Date:
Subject: PGDATESTYLE
Next
From: "Ross J. Reedstrom"
Date:
Subject: Re: PGDATESTYLE