On Sat, Sep 17, 2011 at 10:52 AM, Andreas <maps...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
