PostgreSQL中‘ON CONFLICT DO NOTHING’仍插入新记录的问题排查与解决咨询

这个问题的核心很明确：你的Teams表没有定义任何唯一约束或主键约束，导致ON CONFLICT DO NOTHING根本不知道要检查什么冲突。

先看你的表结构：

CREATE TABLE "Teams" (
 id integer NOT NULL,
 name character varying(255),
 "createdAt" timestamp with time zone NOT NULL,
 "updatedAt" timestamp with time zone NOT NULL,
 abbreviation character varying(255)
);

这里的id只是标记了NOT NULL，但既不是主键，也没有唯一约束；name字段也没有任何唯一性限制。PostgreSQL完全不知道你认为“什么是重复记录”，所以每次执行INSERT时，只要满足非空要求，就会直接插入新行，ON CONFLICT子句相当于摆设。

你需要先给表添加合适的唯一约束，然后让ON CONFLICT知道要检查哪个字段的冲突。下面是几种常见场景的解决方案：

场景1：希望name字段唯一（最符合你的需求） #

先给name字段添加唯一约束：

ALTER TABLE "Teams" ADD CONSTRAINT unique_team_name UNIQUE ("name");

然后修改你的INSERT语句，明确指定冲突检查的字段是name：

INSERT INTO "Teams" ("name","createdAt","updatedAt") 
VALUES ('SOMETHING','2021-01-19 12:33:20.323 +00:00','2021-01-19 12:33:20.323 +00:00') 
ON CONFLICT ("name") DO NOTHING RETURNING *;

这样再重复执行时，PostgreSQL发现name已经存在相同值，就会跳过插入操作。

场景2：如果id是记录的唯一标识 #

如果你的业务逻辑中id是每条记录的唯一键，那应该把它设为主键：

ALTER TABLE "Teams" ADD PRIMARY KEY ("id");

之后如果插入语句中指定了重复的id值，ON CONFLICT DO NOTHING就会生效。不过看你的插入语句没带id，大概率id是自增序列，那场景1更适合你。

场景3：需要多个字段组合唯一 #

比如你要求name和abbreviation组合起来不能重复，可以添加复合唯一约束：

ALTER TABLE "Teams" ADD CONSTRAINT unique_team_name_abbr UNIQUE ("name", "abbreviation");

对应的INSERT语句也要调整为检查这两个字段：

INSERT INTO "Teams" ("name","abbreviation","createdAt","updatedAt") 
VALUES ('SOMETHING','SM','2021-01-19 12:33:20.323 +00:00','2021-01-19 12:33:20.323 +00:00') 
ON CONFLICT ("name", "abbreviation") DO NOTHING RETURNING *;

• 如果不指定冲突字段，ON CONFLICT只会默认使用表的主键作为检查依据，但你的表没有主键，所以这个子句完全不起作用。
• 如果表中已经存在重复的name记录，添加唯一约束会失败，得先清理掉重复数据再执行ALTER TABLE。

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