pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
insert or update within transaction 2+ messages / 2 participants [nested] [flat]
* insert or update within transaction @ 2011-09-17 14:52 Andreas <maps.on@gmx.net> 2011-09-17 15:49 ` Re: insert or update within transaction Josh Kupershmidt <schmiddy@gmail.com> 0 siblings, 1 reply; 2+ messages in thread From: Andreas @ 2011-09-17 14:52 UTC (permalink / raw) To: pgsql-sql Hi, http://www.postgresql.org/docs/current/static/sql-update.html has an example where an either an insert or update is done according if a key already exists. The example is about wines. I did it with numbers. drop table if exists tbl; create table tbl ( key int primary key, val int ); insert into tbl ( key, val ) values ( 1, 1 ), ( 2, 2 ), ( 3, 3 ), ( 8, 8 ); So the key 8 exists. Now I issue the commands according to the example in the docu: BEGIN; -- other operations SAVEPOINT sp1; INSERT INTO tbl VALUES( 8, 15 ); -- Assume the above fails because of a unique key violation, -- so now we issue these commands: ROLLBACK TO sp1; UPDATE tbl SET val = 15 WHERE key = 8; -- continue with other operations, and eventually COMMIT; Instead of the update the query fails with an double key value error for the primary key. Shouldn't the insert fail, get rolled back and then exercute an update instead successfully? Now if this actually worked would be nice but is there a more general statement that does an insert if the key doesn't exist or an update if it allready is there? As I understand if the example above worked, it rolled back the insert in any case and so it is actually equivalent to the update anyway. If the key 8 doesnt't exist the example does actually nothing to the table. ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: insert or update within transaction 2011-09-17 14:52 insert or update within transaction Andreas <maps.on@gmx.net> @ 2011-09-17 15:49 ` Josh Kupershmidt <schmiddy@gmail.com> 0 siblings, 0 replies; 2+ messages in thread From: Josh Kupershmidt @ 2011-09-17 15:49 UTC (permalink / raw) To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql On Sat, Sep 17, 2011 at 10:52 AM, Andreas <maps.on@gmx.net> wrote: > Instead of the update the query fails with an double key value error for the > primary key. > Shouldn't the insert fail, get rolled back and then exercute an update > instead successfully? Yes, and that is indeed what I see when I run your example SQL. At the end, I see this: test=# SELECT * FROM tbl; key | val -----+----- 1 | 1 2 | 2 3 | 3 8 | 15 (4 rows) (i.e. the row with "key" = 8 got updated, settings its "val" = 15) > Now if this actually worked would be nice but is there a more general > statement that does an insert if the key doesn't exist or an update if it > allready is there? Sounds like you're looking for the MERGE statement, which doesn't exist in PostgreSQL yet. You might want to check out: http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE Josh ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2011-09-17 15:49 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2011-09-17 14:52 insert or update within transaction Andreas <maps.on@gmx.net> 2011-09-17 15:49 ` Josh Kupershmidt <schmiddy@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