Re: insert/update

Richard Huxton <dev(at)archonet(dot)com> writes:

> Tom - you don't say precisely what you're trying to do, but I like to keep my
> code simple by making sure there is always a row available.

Or alternatively you could always try to insert the record with a count of 0
then increment. If the insert fails due to a duplicate key violation you could
just ignore the error.

That suffers from doing twice as many queries as necessary all the time. You
could try doing the update then check the result to see how many records were
updated, if 0 then try doing the insert ignoring any errors and then repeat
the update.

But then your code is getting kind of complex... And both of these assume
nobody's deleting records.

The more usual solution is to always try either the update or the insert, and
in the case of a duplicate key violation or 0 updated rows, then try the
other. To do this properly you have to do it in a loop, since some other
process could be inserting or deleting between the two queries.

FWIW the feature you're looking for is indeed a new feature in the latest SQL
standard and there's been some talk of how to implement it in a future version
of Postgres. I would expect to see it come along sometime, though probably not
in 7.5.

--
greg