pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHowto "insert or update" ?
2+ messages / 2 participants
[nested] [flat]
* Howto "insert or update" ?
@ 2010-11-23 03:44 Andreas <maps.on@gmx.net>
0 siblings, 1 reply; 2+ messages in thread
From: Andreas @ 2010-11-23 03:44 UTC (permalink / raw)
To: pgsql-sql
Hi,
is there an elegant way to tell PG :
a) Hey PG, look here are e.g. 3 values A, B, c for tableX
b) please check if there is a row matching A and B as key in tableX
c) if such a row exists, execute an UPDATE on column c else INSERT a
new row.
Currently I have a trigger function that should store a value in tableX
whenever a certain column in tableY gets changed.
I do it with:
a) delete from tableX where key = ( A, B ) ( regardless if there is one )
b) insert into tableX
This seems not very efficient though it works.
Is there a better way?
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Howto "insert or update" ?
@ 2010-11-23 04:11 tv@fuzzy.cz
parent: Andreas <maps.on@gmx.net>
0 siblings, 0 replies; 2+ messages in thread
From: tv@fuzzy.cz @ 2010-11-23 04:11 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
> Currently I have a trigger function that should store a value in tableX
> whenever a certain column in tableY gets changed.
> I do it with:
> a) delete from tableX where key = ( A, B ) ( regardless if there is one
> )
> b) insert into tableX
>
> This seems not very efficient though it works.
> Is there a better way?
Efficiency matters only if the solution is correct, and that's not the
case of your function - there's a quite trivial race condition. Imagine
there are two transactions running at the same time, executing the
function concurrently.
The first one will succeed, while the other one will fail because of
unique constraint violation. A correct solution is something like this
BEGIN
INSERT INTO ...
EXCEPTION
-- the key already exists, so let's update
WHEN unique_violation THEN
UPDATE ...
END;
Regarding efficiency - I'm not aware of a better solution. There are plans
to implement true MERGE but that's in the future. All you can do right now
is to make sure the key is indexed (I guess it's a PK anyway) so that the
INSERT/UPDATE are fast.
Well, actually there's one other thing you could do - you can do a BEFORE
INSERT trigger that checks if the key already exists, and in case it does
switch to UPDATE. Something like
CREATE OR REPLACE FUNCTION my_trigger() RETURNS trigger as $$
BEGIN
UPDATE my_table SET .... WHERE key = NEW.key;
IF (FOUND) THEN
-- updated, do not execute the insert
RETURN NULL;
END IF;
-- execute the insert
RETURN NEW;
END;
$$ language plpgsql;
CREATE TRIGGER merge_trigger BEFORE INSERT ON my_table FOR EACH ROW
EXECUTE PROCEDURE my_trigger();
This is probably more elegant - just execute INSERT statements and it will
handle all the work. The only problem is it does not report the number of
updated rows (it just returns 0 in that case).
But generally it's just as efficient as the solution described above.
regards
Tomas
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-11-23 04:11 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-23 03:44 Howto "insert or update" ? Andreas <maps.on@gmx.net>
2010-11-23 04:11 ` tv@fuzzy.cz
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