Jeff Eckermann wrote:
--- Tom Allison <tallison@tacocat.net> wrote:
I seemed to remember being able to do this but I
can't find the docs.
Can I run a sql query to insert new or update
existing rows in one query?
Otherwise I have to run a select query to see if
it's there and then
another one to update/insert.
This is what you have to do.
This question comes up a lot on the lists. You can
read endless discussions about it if you want to
search the archives.
The issue is concurrency, i.e. multiple users
accessing the data at the same time, and perhaps two
of them wanting to do the same update-else-insert
combination at the same time. Then you have the so
called "race condition", i.e. user1 does a select,
finds the record does not exist, attempts to insert;
in between those, user2 inserts the row. So, you now
either have duplicate data (bad), or user1's insert
fails because of a unique constraint (also bad,
because the operation has failed).
The only way to guarantee against this is to lock the
table for the duration of the exercise, which prevents
any concurrent access at all. This may be acceptable
if you have few users, or a low insert/update load,
but may be a performance killer otherwise.
So I have to watch out for transactions on this?
Essentially what I'm trying to do is one of the following two:
if exists update a field to field+1 on one record
if it doesn't exist, insert a row with field = 1
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
