使用Prisma通过复合键Upsert数据时遇PostgreSQL歧义列错误

问题原因 #

你遇到的错误源于Prisma生成的Upsert SQL中，DO UPDATE SET部分的userIds字段未明确指定归属。PostgreSQL无法区分该字段是来自INSERT语句的待插入值，还是表中已存在的记录值，因此抛出歧义报错。从生成的SQL可清晰看到问题点：

INSERT INTO "public"."Vote" ("filmId", "userIds", "roomName") VALUES ($1, $2, $3) 
ON CONFLICT ("roomName", "filmId") DO UPDATE SET "userIds" = "userIds" || $4 
WHERE (("public"."Vote"."roomName" = $5 AND "public"."Vote"."filmId" = $6) AND 1 = 1) 
RETURNING "public"."Vote"."roomName", "public"."Vote"."filmId", "public"."Vote"."userIds"

这里的"userIds" || $4中的userIds未限定表或别名，引发歧义。

解决方案 #

通过Prisma的raw函数，在update操作中明确指定引用表中的userIds字段，即可消除歧义。以下是两种实现方式：

方式1：参数绑定（安全防SQL注入） #

const votes = await db.vote.upsert({
  where: {
    roomName_filmId: {
      roomName: roomId,
      filmId: filmId,
    },
  },
  update: {
    userIds: prisma.raw('"Vote"."userIds" || $1', [socket.id]),
  },
  create: {
    filmId,
    roomName: roomId,
    userIds: [socket.id],
  },
});

方式2：直接拼接（仅适用于可信输入，存在SQL注入风险） #

const votes = await db.vote.upsert({
  where: {
    roomName_filmId: {
      roomName: roomId,
      filmId: filmId,
    },
  },
  update: {
    userIds: prisma.raw(`"Vote"."userIds" || '{"${socket.id}"}'`),
  },
  create: {
    filmId,
    roomName: roomId,
    userIds: [socket.id],
  },
});

原理说明 #

使用prisma.raw明确指定"Vote"."userIds"后，生成的SQL会将DO UPDATE SET部分修正为：

DO UPDATE SET "userIds" = "Vote"."userIds" || $4

此时PostgreSQL能明确识别出要操作的是表中已存在的userIds字段，彻底消除歧义。

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