Sequelize bulkCreate的updateOnDuplicate报无唯一约束匹配错误

需要批量创建多条数据，若数据已存在则执行更新，数据来源于对象数组。使用Sequelize的bulkCreate方法并配置updateOnDuplicate选项时触发错误：

"[Nest] 23130 - 10/16/2022, 9:54:53 AM ERROR [ExceptionsHandler] there is no unique or exclusion constraint matching the ON CONFLICT specification"

生成的SQL语句如下：

INSERT INTO "daily_price_change" ("date","id","fact_average_cost","plan_average_cost","city_id","createdAt","updatedAt") VALUES ('2022-09-01',DEFAULT,'137838.44','0',1,'2022-10-16 07:15:15.457 +00:00','2022-10-16 07:15:15.457 +00:00'),('2022-09-02',DEFAULT,'137839.22','0',1,'2022-10-16 07:15:15.457 +00:00','2022-10-16 07:15:15.457 +00:00'),('2022-09-03',DEFAULT,'137840.89','0',1,'2022-10-16 07:15:15.457 +00:00','2022-10-16 07:15:15.457 +00:00'),('2022-09-04',DEFAULT,'137843.31','0',1,'2022-10-16 07:15:15.457 +00:00','2022-10-16 07:15:15.457 +00:00'),('2022-09-05',DEFAULT,'137843.27','0',1,'2022-10-16 07:15:15.457 +00:00','2022-10-16 07:15:15.457 +00:00') ON CONFLICT ("date") DO UPDATE SET "fact_average_cost"=EXCLUDED."fact_average_cost" RETURNING "date","id","fact_average_cost","plan_average_cost","city_id","createdAt","updatedAt"

模型中已为date字段添加@Unique注解：

@Table({ tableName: 'daily_price_change' })
export class DailyPriceChangeEntity extends Model {
  @Unique
  @Column({ type: DataType.DATEONLY, allowNull: false })
  date: string; // 2022-10-31

  @Column({
    type: DataType.INTEGER,
    autoIncrement: true,
    primaryKey: true,
    allowNull: false,
  })
  id: number;

  @Column({ type: DataType.FLOAT, allowNull: true, defaultValue: 0 })
  fact_average_cost: number; // 105500.5

  @Column({ type: DataType.FLOAT, allowNull: true, defaultValue: 0 })
  plan_average_cost: number; // 105500.5

  @Column({ type: DataType.INTEGER, allowNull: true })
  city_id: number;
}

Sequelize连接配置如下：

@Module({
  imports: [
    ConfigModule.forRoot({
      envFilePath: '.env',
    }),
    SequelizeModule.forRoot({
      dialect: 'postgres',
      host: process.env.POSTGRES_HOST,
      port: Number(process.env.POSTGRES_PORT),
      username: process.env.POSTGRES_USER,
      password: process.env.POSTGRES_PASSWORD,
      database: process.env.POSTGRES_DB,
      models: [
        Model1,
        DailyPriceChangeEntity,
        Model2,
        Model3,
        Model4,
      ],
      autoLoadModels: true,
      synchronize: true,
    }),
...

移除date字段的唯一约束后，bulkCreate可正常执行但会生成重复数据，求正确实现批量创建或更新的操作。

1. 验证数据库中的唯一约束是否存在 #

虽然模型中添加了@Unique注解，但可能因为synchronize: true未正确同步约束，或表结构未更新。直接登录PostgreSQL执行以下SQL查看约束：

SELECT conname FROM pg_constraint WHERE conrelid = 'daily_price_change'::regclass AND contype = 'u';

如果没有对应date的唯一约束，手动创建：

ALTER TABLE daily_price_change ADD CONSTRAINT unique_date UNIQUE (date);

2. 修正@Unique注解的使用方式 #

单独的@Unique注解可能未正确生成约束，建议改用两种更可靠的方式：

// 方式一：在Column配置中直接声明唯一约束
@Column({ type: DataType.DATEONLY, allowNull: false, unique: true })
date: string;

// 方式二：使用@Unique的数组形式明确指定字段
@Unique(['date'])
@Column({ type: DataType.DATEONLY, allowNull: false })
date: string;

3. 完善bulkCreate的参数配置 #

调用bulkCreate时，明确指定updateOnDuplicate包含需要更新的字段，若需要可指定conflictFields指向唯一约束字段：

await DailyPriceChangeEntity.bulkCreate(dataArray, {
  updateOnDuplicate: ['fact_average_cost', 'plan_average_cost', 'updatedAt'],
  conflictFields: ['date'] // 部分Sequelize版本支持，用于指定冲突匹配字段
});

4. 排查synchronize配置问题 #

如果synchronize: true未同步约束，可能是模型修改后未触发同步。开发环境可临时删除表让Sequelize重新生成，生产环境禁止使用synchronize: true，建议用迁移脚本管理表结构变更。

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