Re: Revisited: Transactions, insert unique.

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