CODEWITHSUNDEEP
postgresqlupsert
davidki
davidki

Reputation: 21

Postgres Insert Into On conflict do update

I want to insert data from one table ("tmp") into another ("tbla"), using the on conflict do update-feature. My Code:

INSERT INTO tbla (id, col1, col2, col3) 
SELECT id, col1, col2, col3 FROM tmp

ON CONFLICT on constraint pkey_tbla DO UPDATE SET col1=tmp.col1 FROM tmp;

DROP TABLE tmp;

This code gives me back an Syntax-Error at "FROM tmp;" Without FROM there is the ERROR: missing FROM-clause entry for table "tmp" Any suggestions on what I'm doing wrong?

DB-Server is running on localhost at a windows 7-machine with postgres 9.5

Upvotes: 2

Views: 3242

Answers (1)

cske
cske

Reputation: 2243

Documentation "Note that the special excluded table is used to reference values originally proposed for insertion" https://www.postgresql.org/docs/9.5/static/sql-insert.html

Fix : ... DO UPDATE SET col1=EXCLUDED.col1;

x=> select * from tbla;
 id | col1 
----+------
  1 |    2
  2 |    3
(2 rows)

x=> truncate tmp;
TRUNCATE TABLE
x=> insert into tmp(id,col1) values (1,42);
INSERT 0 1
x=> INSERT INTO tbla(id,col1) SELECT id,col1 FROM tmp -- wrap line
    ON CONFLICT (id) DO UPDATE SET col1=EXCLUDED.col1;

INSERT 0 1
sh161119=> select * from tbla;
 id | col1 
----+------
  2 |    3
  1 |   42
(2 rows)

Upvotes: 1

Related Questions