Re: insert or update within transaction

On Sat, Sep 17, 2011 at 10:52 AM, Andreas <maps(dot)on(at)gmx(dot)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