Oliver,
> (Unfortunately, you cannot use a transaction, because
> the failing UPDATE would abort the transaction. However,
> I believe the nested-transaction feature of PostgreSQL 8
> (still in beta) would allow to do such things. Someone
> please correct me if I'm wrong ...)
You are correct. However, he can do it right now the other way around, if
it can be nested into a plpgsql function or done with libpq: do an UPDATE,
check the number of rows affected, and if it's 0, do an insert.
By 8.1/8.2 we'll likely have implemented the new SQL spec for this sort of
operation, and this common problem will go away.
Mind you, it's not a problem I've ever personally had. I'm actually a bit
puzzled about how the application could NOT know whether it's handling a new
or a modified row; makes me wonder about people's application design.
--
Josh Berkus
Aglio Database Solutions
San Francisco
