Thread: Doing a conditional insert/update

Hello.<br /><br />I'm currently developing my first web app with Postgres and I have a question. <br /><br />How do I
performa conditional insert/update? <br /><br />To clarify: I need to insert data into a table when the primary key is
notalready in the table, or an update if it is. I have currently solved this by SELECTing for the primary key, then
lookingif there is a row, and if there is I do an UPDATE otherwise I do an INSERT. But since this is a web app this way
ofcourse leaves a big race condition. <br /><br />Is there a way to get around that? Like the ON DUPLICATE KEY UPDATE
statementin MySQL?<br /><br /><br />Thanks for your time,<br /><br />Holli<span style="font-family: monospace;"><br
/></span><br/> 

On Thu, Apr 19, 2007 at 08:27:30PM +0200, Markus Holzer wrote:
> How do I perform a conditional insert/update?

The short answer is that you can't, at least not the way you want to.

> already in the table, or an update if it is. I have currently solved this by
> SELECTing for the primary key, then looking if there is a row, and if there
> is I do an UPDATE otherwise I do an INSERT. But since this is a web app this
> way of course leaves a big race condition.

Only if you don't do it in one transaction.  If you use a
savepoint to do this, then you can BEGIN, SELECT FOR UPDATE, then if
you get it UPDATE; if you don't, start a savepoint, insert, and if
you don't get a failure, commit the whole thing.  If you _do_ get a
failure, you rollback the savepoint, and then do the update.  There's
a (cleaner) example of this in the UPDATE doc from the 8.2 release.

But I have doubts that your program design is right if this is the
approach you're trying to take (it's not impossible that it's the
right way, just that this is often a workaround for a dodgy data
model in the first place).  What is the conflict you're trying to
avoid?

A
-- 
Andrew Sullivan  | ajs@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.    --Philip Greenspun

On 4/19/07, Markus Holzer <holli.holzer@googlemail.com> wrote:
> Hello.
>
> I'm currently developing my first web app with Postgres and I have a
> question.
>
> How do I perform a conditional insert/update?
>
> To clarify: I need to insert data into a table when the primary key is not
> already in the table, or an update if it is. I have currently solved this by
> SELECTing for the primary key, then looking if there is a row, and if there
> is I do an UPDATE otherwise I do an INSERT. But since this is a web app this
> way of course leaves a big race condition.
>
> Is there a way to get around that? Like the ON DUPLICATE KEY UPDATE
> statement in MySQL?
>
>
> Thanks for your time,
>
> Holli

--------------------------------------------------

CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN   LOOP       UPDATE db SET b = data WHERE a = key;       IF found THEN           RETURN;       END IF;
       BEGIN           INSERT INTO db(a,b) VALUES (key, data);           RETURN;       EXCEPTION WHEN unique_violation
THEN          -- do nothing       END;   END LOOP;
 
END;
$$
LANGUAGE plpgsql;

SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');

--------------------------------------------------

Straight from the docs:

http://www.postgresql.org/docs/8.2/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING