PostgreSQL INSERT ON CONFLICT语句报错及RETURNING子句使用咨询

问题背景 #

现有三张表结构：

• users：id（自增主键PK）、Name
• articles：id（自增主键PK）、author_id（外键FK关联users.id）
• notes：id（自增主键PK）、author_id（外键FK关联users.id）、article_id（外键FK关联articles.id）

尝试执行三次INSERT ON CONFLICT（Upsert）语句均报错，具体如下：

错误1 #

执行语句：

INSERT INTO notes (author_id, article_id)
VALUES(1,1) 
ON CONFLICT ON CONSTRAINT author_id 
DO NOTHING;

报错信息：

ERROR: constraint "author_id" for table "notes" does not exist

原因：ON CONFLICT ON CONSTRAINT后需指定唯一约束/排他约束的名称，author_id是字段名而非约束名，且外键约束不能作为Upsert的冲突判断依据。

错误2 #

执行语句：

INSERT INTO notes (author_id, article_id)
VALUES(1,1) 
ON CONFLICT ON CONSTRAINT author_id REFERENCES 'users'
DO NOTHING;

报错信息：

ERROR: syntax error at or near "REFERENCES"

原因：语法错误，ON CONFLICT ON CONSTRAINT仅能直接指定约束名称，不能附加REFERENCES这类外键声明语句。

错误3 #

执行语句：

INSERT INTO notes (author_id, article_id)
VALUES(1,1) 
ON CONFLICT (author_id)
DO NOTHING;

报错信息：

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

原因：PostgreSQL的Upsert要求冲突判断的字段/约束必须是唯一约束（UNIQUE）或排他约束（EXCLUSION），外键约束不满足该条件。你仅给author_id添加了外键，未设置唯一约束，因此无法用它作为冲突判断依据。

核心解决方案 #

Upsert的冲突判断依赖唯一/排他约束，与外键无关。若要实现「当author_id和article_id的组合已存在时不插入新记录」，需先给notes表的(author_id, article_id)组合添加唯一约束：

ALTER TABLE notes ADD CONSTRAINT unique_author_article UNIQUE (author_id, article_id);

添加约束后，即可执行正确的Upsert语句：

-- 通过字段组合指定冲突条件
INSERT INTO notes (author_id, article_id)
VALUES(1,1) 
ON CONFLICT (author_id, article_id) DO NOTHING;

-- 或者通过约束名指定
INSERT INTO notes (author_id, article_id)
VALUES(1,1) 
ON CONFLICT ON CONSTRAINT unique_author_article DO NOTHING;

关于returning id的问题 #

完全可以在语句末尾添加returning id获取note的id：

• 若为新插入的记录，会返回对应的id；
• 若因冲突执行DO NOTHING，则无返回结果。

示例语句：

INSERT INTO notes (author_id, article_id)
VALUES(1,1) 
ON CONFLICT (author_id, article_id) DO NOTHING
RETURNING id;

如果想在冲突时也返回已存在的记录id，可改为DO UPDATE（即使不修改字段）：

INSERT INTO notes (author_id, article_id)
VALUES(1,1) 
ON CONFLICT (author_id, article_id) 
DO UPDATE SET author_id = EXCLUDED.author_id -- 无实际更新，仅触发返回
RETURNING id;

内容的提问来源于stack exchange，提问作者eruc