PostgreSQL 14中如何将UPDATE查询转换为Upsert语句

前提准备 #

首先要确保main.item_vendor表在item_id字段上有唯一约束（主键或唯一索引），否则ON CONFLICT无法生效。如果没有，先执行以下语句创建：

ALTER TABLE "main"."item_vendor" ADD CONSTRAINT item_vendor_item_id_unique UNIQUE ("item_id");

改写后的Upsert语句 #

原UPDATE语句通过关联main.item表更新符合条件的item_vendor记录，改写为Upsert后可自动处理存在则更新、不存在则插入的逻辑：

INSERT INTO "main"."item_vendor" ("item_id", "vendor_id", "audit_by")
SELECT 
    "main"."item"."id" AS "item_id",
    $1 AS "vendor_id",
    $2 AS "audit_by"
FROM "main"."item"
WHERE "main"."item"."id" IN ($3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17)
ON CONFLICT ("item_id") DO UPDATE
SET 
    "vendor_id" = EXCLUDED."vendor_id",
    "audit_by" = EXCLUDED."audit_by";

参数对应说明 #

参数数组和你原UPDATE的完全一致：

values: [
    '28',        // $1: 要设置的vendor_id
    17355915,    // $2: 要设置的audit_by
    '188646',    // $3: 目标item的id
    '188647',    // $4: 目标item的id
    '188648',    // $5: 目标item的id
    '188649',    // $6: 目标item的id
    '188650',    // $7: 目标item的id
    '188749',    // $8: 目标item的id
    '188750',    // $9: 目标item的id
    '188751',    // $10: 目标item的id
    '188752',    // $11: 目标item的id
    '188753',    // $12: 目标item的id
    '200000691', // $13: 目标item的id
    '200000693', // $14: 目标item的id
    '200000695', // $15: 目标item的id
    '200000697', // $16: 目标item的id
    '200000699'  // $17: 目标item的id
]

逻辑说明 #

1. 插入逻辑：从main.item中筛选出指定ID的记录，将对应的item.id、指定的vendor_id和audit_by插入到item_vendor中，如果该item_id在item_vendor中不存在，直接生成新记录。
2. 更新逻辑：如果item_id已存在于item_vendor中，触发冲突后，将该记录的vendor_id和audit_by更新为本次待插入的参数值（EXCLUDED代表触发冲突的那条待插入记录）。

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