如何在 postgres 9.5 中正确执行 upsert

Question

使用 postgresql 9.5 的 upsert 语法正确，下面的查询显示 column reference "gallery_id" is ambiguous 错误，为什么？

 var dbQuery = `INSERT INTO category_gallery (
  category_id, gallery_id, create_date, create_by_user_id
  ) VALUES ($1, $2, $3, $4)
  ON CONFLICT (category_id)
  DO UPDATE SET
  category_id = $1,
  last_modified_date = $3,
  last_modified_by_user_id = $4
  WHERE gallery_id = $2`;

我尝试将 WHERE gallery_id = $2; 更改为 WHERE category_gallery.gallery_id = $2; 然后显示错误 there is no unique or exclusion constraint matching the ON CONFLICT specification ，但 我不想将 gallery_id 或 category_id 设置为唯一， 因为我想确定 column同样的然后做更新….

如何在 postgres 9.5 中正确执行 upsert？

如果 ON CONFLICT 需要唯一列，我应该使用其他方法，如何？

我想确定多个列都冲突然后更新，正确的用法是什么

var dbQuery = `INSERT INTO category_gallery (
  category_id, gallery_id, create_date, create_by_user_id
  ) VALUES ($1, $2, $3, $4)
  ON CONFLICT (category_id, gallery_id)
  DO UPDATE SET
  category_id = $1,
  last_modified_date = $3,
  last_modified_by_user_id = $4
  WHERE gallery_id = $2`;

var dbQuery = `INSERT INTO category_gallery (
  category_id, gallery_id, create_date, create_by_user_id
  ) VALUES ($1, $2, $3, $4)
  ON CONFLICT (category_id AND gallery_id)
  DO UPDATE SET
  category_id = $1,
  last_modified_date = $3,
  last_modified_by_user_id = $4
  WHERE gallery_id = $2`;

表（category_id，gallery_id 不是唯一列）

 category_id | gallery_id | create_date | create_by_user_id | last_modified_date | last_modified_by_user_id
1 | 1 | ...
1 | 2 | ...
2 | 2 | ...
1 | 3 | ...

原文由 user1575921 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

ON CONFLICT 构造需要 UNIQUE 约束才能工作。从 INSERT .. ON CONFLICT 子句 的文档中：

可选的 ON CONFLICT 子句指定引发 唯一违规 或 排除约束 违规错误的替代操作。对于建议插入的每个单独的行，插入继续进行，或者，如果违反了由冲突目标指定的仲裁约束或索引，则采取替代冲突行动。 ON CONFLICT DO NOTHING 只是避免插入一行作为其替代操作。 ON CONFLICT DO UPDATE 更新与建议插入的行冲突的现有行作为其替代操作。

现在，问题不是很清楚，但您可能需要 UNIQUE 对 2 列组合的约束： (category_id, gallery_id) 。

 ALTER TABLE category_gallery
    ADD CONSTRAINT category_gallery_uq
    UNIQUE (category_id, gallery_id) ;

如果要插入的行与表中已有的行匹配 两个 值，则代替 INSERT ，执行 UPDATE ：

 INSERT INTO category_gallery (
  category_id, gallery_id, create_date, create_by_user_id
  ) VALUES ($1, $2, $3, $4)
  ON CONFLICT (category_id, gallery_id)
  DO UPDATE SET
    last_modified_date = EXCLUDED.create_date,
    last_modified_by_user_id = EXCLUDED.create_by_user_id ;

您可以使用 UNIQUE 约束的任一列：

   ON CONFLICT (category_id, gallery_id)

或约束名称：

   ON CONFLICT ON CONSTRAINT category_gallery_uq

原文由 ypercubeᵀᴹ 发布，翻译遵循 CC BY-SA 3.0 许可协议