Re: how do I update or insert efficently in postgres

marc(at)oscar(dot)eng(dot)cv(dot)net (Marc Spitzer) writes:
> I need to do the follwoing logic for a db I am building:
> if row exists update some fields
> else insert all fields

> I have come across this befor and have used select to drive the
> choice, if I could get the row update else insert.

Which case do you think will be more common? If UPDATE is the more
common scenario then it's a win to do

UPDATE set modifiable-fields = whatever WHERE key = whatever
if (zero rows updated)
INSERT ...

Alternatively you can do

INSERT ...
if (fail due to duplicate key)
UPDATE ...

if you think INSERT is the more common case. (This all assumes you
have a unique key for the table, but if you don't, then what do you
mean by "the row already exists"?)

Neither of these are perfect, however. The former has a race condition
if two clients might try to insert the same key at about the same time.
You can improve it to

BEGIN;
UPDATE set modifiable-fields = whatever WHERE key = whatever
if (zero rows updated)
{
INSERT ...
if (fail due to duplicate key)
{
ABORT;
loop back to BEGIN;
}
}
COMMIT;

but this is kinda ugly. (Of course, if you could have two clients
independently inserting/updating the same row at the same time, you
have problems anyway: which one should win, and why? I think the
coding difficulty may tell you you have a design problem.)

As for the INSERT-then-UPDATE approach, you have the same problem
that you have to ABORT and start a new transaction if the INSERT
fails. This is uncool if you really want the whole thing to be part
of a larger transaction.

But as long as you've guessed right about which case is more common,
you have only one query most of the time.

regards, tom lane