Postgresql upsert query

Question

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?

1

no, it isn't a duplicate – potapuff Sep 09 '11 at 12:12

score 2 · Accepted Answer · answered Sep 09 '11 at 14:39

2

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

answered Sep 09 '11 at 14:39

Matt

4,515
5
22
29

"In answer to your syntax error question you are trying to do is not currently possible." Ok. But why? in documentation to 8.4 is written "The optional RETURNING clause causes UPDATE to compute and return value(s) based on each row actually updated. Any expression using the table's columns, and/or columns of other tables mentioned in FROM, can be computed. The new (post-update) values of the table's columns are used. The syntax of the RETURNING list is identical to that of the output list of SELECT." but SELECT (UPDATE ... RETURNING ...) throw syntax error – potapuff Sep 12 '11 at 10:26
Question is duplicate of http://stackoverflow.com/questions/7191902/cannot-select-from-update-returning-clause-in-postgres – potapuff Sep 12 '11 at 16:27

Postgresql upsert query

1 Answers1