Skip site navigation (1) Skip section navigation (2)

Re: insert/update

From: Tom Allison <tallison(at)tacocat(dot)net>
To: Jeff Eckermann <jeff_eckermann(at)yahoo(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: insert/update
Date: 2004-05-26 21:02:02
Message-ID: 40B505CA.6020503@tacocat.net (view raw or flat)
Thread:
Lists: pgsql-general
Jeff Eckermann wrote:
> --- Tom Allison <tallison(at)tacocat(dot)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


In response to

Responses

pgsql-general by date

Next:From: Moharar, Mausumi (YBUSA-CDR)Date: 2004-05-26 21:17:17
Subject: Problem to run MasterInit for Rserv in Mirroring of Databases
Previous:From: Tom LaneDate: 2004-05-26 20:31:27
Subject: Re: Problem with sequences on a reload of a pg_dump file

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group