pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDoing a conditional insert/update
3+ messages / 3 participants
[nested] [flat]
* Doing a conditional insert/update
@ 2007-04-19 18:27 Markus Holzer <holli.holzer@googlemail.com>
2007-04-19 18:55 ` Re: Doing a conditional insert/update Andrew Sullivan <ajs@crankycanuck.ca>
2007-04-19 18:58 ` Re: Doing a conditional insert/update Rodrigo De León <rdeleonp@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Markus Holzer @ 2007-04-19 18:27 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Doing a conditional insert/update
2007-04-19 18:27 Doing a conditional insert/update Markus Holzer <holli.holzer@googlemail.com>
@ 2007-04-19 18:55 ` Andrew Sullivan <ajs@crankycanuck.ca>
1 sibling, 0 replies; 3+ messages in thread
From: Andrew Sullivan @ 2007-04-19 18:55 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Doing a conditional insert/update
2007-04-19 18:27 Doing a conditional insert/update Markus Holzer <holli.holzer@googlemail.com>
@ 2007-04-19 18:58 ` Rodrigo De León <rdeleonp@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Rodrigo De León @ 2007-04-19 18:58 UTC (permalink / raw)
To: pgsql-sql; +Cc: Markus Holzer <holli.holzer@googlemail.com>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-04-19 18:58 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-19 18:27 Doing a conditional insert/update Markus Holzer <holli.holzer@googlemail.com>
2007-04-19 18:55 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-04-19 18:58 ` Rodrigo De León <rdeleonp@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox