Alfred Perlstein <bright@wintelcom.net> writes:
> (yes, I just thought about only indexing, and trying the update
> first and only on failure doing an insert, however we really can't
> determine if the initial update failed because no record matched(ok),
> or possible some other error (ouch))
Uh ... why not? "UPDATE 0" is a perfectly recognizable result
signature, it seems like. (I forget just how that looks at the
libpq API level, but if psql can see it so can you.)
Alternatively, if you think the insert is more likely to be the
right thing, try it first and look to see if you get a "can't
insert duplicate key into unique index" error.
You're right that SQL provides no combination statement that would
allow these sequences to be done with only one index probe. But
FWIW, I'd think that the amount of wasted I/O would be pretty minimal;
the relevant index pages should still be in the buffer cache when
the second query gets to the backend.
regards, tom lane