From: Andreas <maps.on@gmx.net>
To: pgsql-sql@postgresql.org
Subject: insert or update within transaction
Date: Sat, 17 Sep 2011 16:52:55 +0200
Message-ID: <4E74B447.1080509@gmx.net> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: maps.on@gmx.net
Subject: Re: insert or update within transaction
In-Reply-To: <4E74B447.1080509@gmx.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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