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