为什么Sequelize的upsert方法在使用复合唯一键时无效？

Question

我在 PostgreSQL 数据库中使用这个表：

 create table if not exists "Service" (
    _id uuid not null primary key,
    service text not null,
    "count" integer not null,
    "date" timestamp with time zone,
    team uuid,
    organisation uuid,
    "createdAt" timestamp with time zone not null,
    "updatedAt" timestamp with time zone not null,
    unique (service, "date", organisation),
    foreign key ("team") references "Team"("_id"),
    foreign key ("organisation") references "Organisation"("_id")
);

当我尝试使用以下代码进行Sequelize的upsert时，它会抛出一个错误：

Service.upsert({ team, date, service, organisation, count }, { returning: true })

错误信息如下：

错误：违反唯一约束条件“Service_service_date_organisation_key”的重复键值

（service，date，organisation）=（xxx，2022-12-30 01:00:00+01，12345678-5f63-1bc6-3924-517713f97cc3）已经存在。

但根据Sequelize文档，它应该可以正常工作：https://sequelize.org/docs/v6/other-topics/upgrade/#modelupsert

Postgres用户请注意：如果upsert负载包含PK字段，则将使用PK作为冲突目标。否则，将选择第一个唯一约束作为冲突键。

我该如何找到这个重复键错误，并使用复合唯一键unique (service, "date", organisation)使其正常工作？

- rap-2-h

---

第一个唯一约束将被选为冲突键 - 我想知道按什么顺序。这可能意味着，如果负载中没有PK，但PK恰好是第一个唯一约束，则仍将针对PK。如果根据名称的顺序选择约束，则可以重命名约束，使您的约束排在第一位。如果跳过PK，但在您之前选择了其他某些约束 - 您可以列出它们 - 您可以只需删除/重新创建/重命名该约束。 - Zegarek

Answer 1

看起来你的问题与问题#13240有关。

如果你使用的是Sequelize 6.12或更高版本，你应该可以使用一个显式列表的conflictFields：

Service.upsert(
    { team, date, service, organisation, count }, 
    { conflictFields: ["service", "date", "organisation"] },
    { returning: true }
)

- Zegarek

---

如果conflictFields不是唯一索引，那么这在Postgres中是无效的，对吗？ - koFTT

没错。必须有一个约束条件，以便insert操作违反该约束条件，从而通过update操作来处理违规情况。列出的conflictFields字段用于根据使用它们的索引猜测您希望处理的约束条件。在普通的SQL语句中，您还可以直接命名特定的约束条件。关于on conflict...do update的更多信息，请参阅此处。关于constraints的更多信息，请参阅此处。 - Zegarek

没错。必须有一个约束条件，以便insert操作违反该约束条件，从而通过update操作来处理违规情况。列出的conflictFields字段用于根据使用它们的索引猜测您希望处理的约束条件。在普通的SQL中，您还可以直接命名特定的约束条件。有关on conflict...do update的更多信息，请参阅此处。有关constraints的更多信息，请参阅此处。 - undefined

Answer 2

参考资料

类似的问题已经在GitHub上提出，请参见：

• https://github.com/sequelize/sequelize/issues/13240
• https://github.com/sequelize/sequelize/issues/13412

目前这些问题尚未得到解决，因此在撰写本文时，该问题似乎仍未解决，因此您将需要采取解决方法。下面我将提供一些解决此问题的想法，但由于我从未使用过Sequelize，因此我可能存在一些语法错误或误解。如果是这样，请指出来，我会进行更正。

方法1：通过唯一键查询并插入/更新记录

Post.findAll({
  where: {
    service: yourservice,
    date: yourdate,
    organization: yourorganization
  }
});

如果结果为空，则进行插入，否则进行更新。

方法2：修改模式

由于您的复合unique键是候选键，一种选择是删除_id字段，并使您的(service，“date”，organization)唯一。

方法3：在表上实现插入触发器

您可以简单地从Sequelize调用insert，并让PostgreSQL trigger处理upserting，请参见：如何在PostgreSQL中编写upsert触发器？

示例触发器：

CREATE OR REPLACE FUNCTION on_before_insert_versions() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF pg_trigger_depth() = 1 THEN
      INSERT INTO versions (key, version) VALUES (NEW.key, NEW.version)
         ON CONFLICT (key)
         DO UPDATE SET version = NEW.version;
      RETURN NULL;
   ELSE
      RETURN NEW;
   END IF;
END;$$;

当然，您需要根据您的模式和命令相应地更改表名和字段名。

- Lajos Arpad