Postgresql INSERT ON CONFLICT syntax

Question

欢迎使用以下测试表:

CREATE TEMP SEQUENCE pid start 10000;
SELECT nextval ('pid');
CREATE TEMP TABLE project_test (
id int DEFAULT nextval('pid') primary key,
project_id int UNIQUE NOT NULL
);

我试图做一个语句，插入一个新的值为project_id返回id或返回id，如果project_id存在。

我已经尝试了以下操作:

INSERT INTO project_test(project_id) VALUES(50) RETURNING id 
ON CONFLICT (project_id) DO UPDATE project_test SET project_id = 50 WHERE project_id = 50 RETURNING id;

我已经阅读了这里的文档:https://www.postgresql.org/docs/13/sql-insert.html我在Postgres 13

我得到一个语法错误

ERROR:在"ON"处或附近有语法错误

提前感谢您的提示

Answer 1

INSERT INTO project_test(project_id) VALUES(50)
ON CONFLICT (project_id) DO UPDATE SET project_id = 50 WHERE project_test.project_id = 50 RETURNING id;

问题是返回id需要在末尾，然后它认为列引用"project_id"模棱两可，这很奇怪。谢谢所有的