如何修复PostgreSQL报错：无匹配ON CONFLICT的唯一/排除约束

我正在使用PostgreSQL 15.4，创建了如下topics表：

create table public.topics
(
    id              serial constraint "PK_e4aa99a3fa60ec3a37d1fc4e853" primary key,
    created_at      timestamp         default now()                        not null,
    last_updated_at timestamp         default now(),
    uuid            varchar(64)                                            not null,
    stage           topics_stage_enum default 'dynamic'::topics_stage_enum not null,
    name            varchar(256)                                           not null 
                    constraint idx_unique_topics_topic unique,
    sanitized_name  varchar(256)                                           not null 
                    constraint idx_unique_topics_sanitized unique
);


create index "IDX_5422249b54115966f4676e3bcd"
    on public.topics (created_at);

create index "IDX_41a25cc9eb73ff4225afd9d5a2"
    on public.topics (last_updated_at);

create index "IDX_55e32ccadfd3178c768652ddeb"
    on public.topics (stage);

create index "IDX_1304b1c61016e63f60cd147ce6"
    on public.topics (name);

create index "IDX_9b7b898f847194409c53b1abc8"
    on public.topics (sanitized_name);

尝试通过ON CONFLICT执行批量upsert，TypeORM生成的SQL语句如下：

INSERT INTO "topics"("created_at", "last_updated_at", "uuid", "stage", "name", "sanitized_name") 
VALUES (DEFAULT, DEFAULT, $1, $2, $3, $4) 
ON CONFLICT ( "name", "sanitized_name" ) 
DO UPDATE SET "name" = EXCLUDED."name", "sanitized_name" = EXCLUDED."sanitized_name", 
"uuid" = EXCLUDED."uuid", "stage" = EXCLUDED."stage"  
RETURNING "id", "created_at", "last_updated_at", "stage"

执行时出现报错：

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

我已为name和sanitized_name分别设置唯一约束，按理解应可正常运行，请问该如何修复此问题？

修复方案 #

问题根源 #

你为name和sanitized_name设置的是单个字段的独立唯一约束，但ON CONFLICT子句中指定的是两个字段的组合冲突条件。PostgreSQL要求冲突条件必须对应一个已存在的联合唯一约束/索引，单个字段的唯一约束无法满足组合冲突的检测需求，因此报错。

具体修复步骤 #

1. 添加联合唯一约束
   执行以下SQL语句，为name和sanitized_name创建联合唯一约束：

   ALTER TABLE public.topics
   ADD CONSTRAINT idx_unique_topics_name_sanitized UNIQUE (name, sanitized_name);
   

2. （可选）调整TypeORM实体配置
   如果是通过TypeORM实体定义自动生成表结构，需要修改实体类，将原来单个字段的unique: true替换为联合唯一配置：

   @Entity()
   export class Topic {
       // 其他字段定义...
   
       @Column({ unique: false })
       name: string;
   
       @Column({ unique: false })
       sanitizedName: string;
   
       // 在类级别添加联合唯一约束
       @Unique(['name', 'sanitizedName'])
   }
   

   注意要移除原来单个字段的unique: true配置，避免重复创建不必要的约束。

额外说明 #

如果你的业务逻辑是只要name或sanitized_name任意一个字段重复就触发更新，那么不能使用当前的组合冲突条件，需要拆分Upsert逻辑或调整冲突检测策略。但从你提供的SQL语句来看，业务需求应该是当name和sanitized_name同时重复时才执行更新，因此添加联合唯一约束是最直接的解决方案。

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