如何在9.5级考试中正确地进行upsert

Question

使用postgresql 9.5，查询显示列引用"gallery_id"是不明确的错误，为什么？

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 至目录_画廊。画廊id=2美元然后显示错误没有唯一或排除约束匹配冲突规范，但我不想将gallery_id或category_id设置为唯一，因为我想确保两列相同，然后进行更新。。。。

如何正确地在postgres 9.5中进行upsert？

如果冲突上的需要唯一列，我应该使用其他方法吗，如何？





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

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`;

表（类别id、画廊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

作为当前可接受答案的简化替代方案，UNIQUE约束可以在创建表时匿名添加：

CREATE TABLE table_name (
    id  TEXT PRIMARY KEY,
    col TEXT,
    UNIQUE (id, col)
);

然后，upsert查询变为（与已回答的内容类似）：

INSERT INTO table_name (id, col) VALUES ($1, $2)
ON CONFLICT (id, col)
    DO UPDATE SET col = $2;

Answer 2

冲突上的构造需要唯一的约束才能工作。从INSERT。。关于冲突条款：

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

现在，问题还不是很清楚，但您可能需要对组合的两列进行唯一的约束：（category\u id，gallery\u id）。

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

如果要插入的行与表中已经存在的行的两个值匹配，则执行UPDATE而不是INSERT：

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