如何在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设置为唯一， 因为我想确保两列都相同然后进行更新....

如何正确地在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 | ...

Answer 1

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

可选ON CONFLICT子句指定了引发 唯一违反 或 排除约束
违反错误的替代操作。对于建议插入的每个单独行，要么继续插入，要么如果违反了由conflict_target指定的仲裁者约束或索引，则采用替代性的action_action。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