Postgres Insert Insert On conflict update

Question

我想使用on conflict do update-feature将数据从一个表("tmp")插入到另一个表("tbla")中。我的代码：

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;

这段代码在"FROM tmp;“上返回了一个语法错误，如果没有FROM，就会出现错误:缺少表"tmp”的FROM-子句条目。有什么建议吗？

DB-Server在装有postgres 9.5的windows 7机器上的localhost上运行

Answer 1

Documentation“请注意，特殊的排除表用于引用最初建议插入的值”https://www.postgresql.org/docs/9.5/static/sql-insert.html

修复：... 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)