PostgreSQL中INSERT ON CONFLICT UPDATE功能异常排查

你的错误根源是对ON CONFLICT执行逻辑的误解：PostgreSQL会先尝试执行完整的INSERT操作，只有当INSERT触发指定的唯一约束冲突时，才会进入DO UPDATE分支。

也就是说，当你执行这条语句时：

• 如果目标id不存在，PostgreSQL会尝试插入新行，但你没给col3传值，它会被默认设为NULL，直接触发NOT NULL约束错误，根本没机会走到UPDATE步骤。
• 只有当id已存在时，才会跳过INSERT，执行UPDATE操作，此时现有行的col3值会被保留（因为你UPDATE里没修改它）。

解决方式 #

• 插入新行必须满足约束：如果需要支持插入新行，必须在INSERT部分提供col3的有效值，或者给col3设置数据库层面的默认值（比如ALTER TABLE tbl ALTER COLUMN col3 SET DEFAULT '默认值';）。
• 仅处理冲突更新场景：如果你的业务逻辑只需要在id冲突时更新现有行，不需要插入新行，可以考虑改用单独的UPDATE语句；但如果还是想用ON CONFLICT，必须确保INSERT部分满足所有列的约束（包括col3）。
• 冲突时保留原列值：如果在冲突更新时不想修改col3，不需要在UPDATE里指定它，现有值会自动保留，不用额外传入。

举个调整后的示例（假设插入新行时col3有默认值，或者你传入了有效值）：

INSERT INTO "public"."tbl" ("id", "col1", "col2", "col3") 
VALUES ($1, $2, $3, $7) 
ON CONFLICT ("id") DO UPDATE 
SET "col1" = $5, "col2" = $6 
RETURNING "id";

这里UPDATE部分没碰id和col3，冲突时会保留原id（其实也没必要更新id，因为冲突就是因为id重复）和col3的值。

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