如何在 postgres 9.5 中正确执行 upsert

使用 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; to WHERE category_gallery.gallery_id = $2;然后显示错误there is no unique or exclusion constraint matching the ON CONFLICT specification, but 我不想将 gallery_id 或 Category_id 设置为唯一因为我想确保两列相同然后更新......

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

if 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 | ...

The ON CONFLICT构造需要一个UNIQUE工作的约束。从文档上INSERT .. ON CONFLICT clause http://www.postgresql.org/docs/9.5/static/sql-insert.html#SQL-ON-CONFLICT:

可选的ON CONFLICT条款规定了提出问题的替代行动独特的违规行为 or 排除约束违规错误。对于建议插入的每个单独行，要么继续插入，要么如果违反了conflict_target指定的仲裁器约束或索引，则采取替代conflict_action。ON CONFLICT DO NOTHING只是避免插入行作为其替代操作。ON CONFLICT DO UPDATE更新与建议插入的行冲突的现有行作为其替代操作。

现在，问题不是很清楚，但你可能需要一个UNIQUE对两列组合的约束：(category_id, gallery_id).

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

如果要插入的行匹配both值已经在表中，而不是INSERT, do an 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