Postgresql upsert query [duplicate]

This question already has answers here: Closed 10 years ago.

Possible Duplicate:

Insert, on duplicate update (postgresql)

Cannot SELECT from UPDATE RETURNING clause in postgres

Help to understand me syntax error. I try to make such implementation of upsert query in PosgreSql:

create table tbl( key  int, val int);

insert into tbl(key,val)
   select distinct(key), 0 from unnest('{0,1,2,3,4,5}'::int[]) as key
       where key not in (
         update tbl set val = 1
             where key = any('{0,1,2,3,4,5}'::int[])
         returning key
);

error is :

ERROR:  syntax error at or near "tbl"
ROWS 6:  update tbl set val = 1
                  ^

********** Error **********

ERROR: syntax error at or near "tbl"
SQL state: 42601
Character: 167

But update subquery without insert part work well.

Is any easiest way to开发者_开发知识库 make upsert query?

---

Your question was suggested as a duplicate because a solution to PostgreSQL not having an UPSERT command was already posted, which does answer your question of how to implement UPSERT.

In answer to your syntax error question you are trying to do is not currently possible. You will find that a variation of it is possible in the upcoming version 9.1 of PostgreSQL, which will support data modifying statements in a WITH clause: http://www.postgresql.org/docs/9.1/static/queries-with.html#QUERIES-WITH-MODIFYING