如何在Postgres 9.5中正确进行upsert [英] How to correctly do upsert in postgres 9.5

问题描述

使用postgresql 9.5的upsert的正确语法，下面的查询显示列引用 gallery_id不明确错误，为什么？

correct syntax of upsert with postgresql 9.5, below query shows column reference "gallery_id" is ambiguous error , why?

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; 然后显示错误，没有匹配ON CONFLICT规范的唯一约束或排除约束，但我不想将gallery_id或category_id设置为唯一，因为我想确保两列都相同，然后进行更新...。

I tried change WHERE gallery_id = $2; to WHERE category_gallery.gallery_id = $2; then shows error there is no unique or exclusion constraint matching the ON CONFLICT specification, but I don't want to set gallery_id or category_id as unique becuase I want to sure both column are same then do update....

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

How to correctly do upsert in postgres 9.5?

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

if ON CONFLICT need unique column, should I use other method, how?

我想确保多列都冲突然后进行更新，这是什么正确用法

I want to sure multiple column both conflict then do update, what is correct usage

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不是唯一列）

table (category_id , gallery_id not unique column)

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

推荐答案

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

The ON CONFLICT construct requires a UNIQUE constraint to work. From the documentation on INSERT .. ON CONFLICT clause:

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

The optional ON CONFLICT clause specifies an alternative action to raising a unique violation or exclusion constraint violation error. For each individual row proposed for insertion, either the insertion proceeds, or, if an arbiter constraint or index specified by conflict_target is violated, the alternative conflict_action is taken. ON CONFLICT DO NOTHING simply avoids inserting a row as its alternative action. ON CONFLICT DO UPDATE updates the existing row that conflicts with the row proposed for insertion as its alternative action.

现在，这个问题不是很清楚，但是您可能需要在合并的2列上使用 UNIQUE 约束：（category_id， gallery_id）。

Now, the question is not very clear but you probably need a UNIQUE constraint on the 2 columns combined: (category_id, gallery_id).

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

如果要插入的行与两者值匹配，且已有行表，然后执行 UPDATE 而不是 INSERT ：

If the row to be inserted matches both values with a row already on the table, then instead of 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约束的任一列：

You can use either the columns of the UNIQUE constraint:

  ON CONFLICT (category_id, gallery_id) 

或约束名称：

  ON CONFLICT ON CONSTRAINT category_gallery_uq  

这篇关于如何在Postgres 9.5中正确进行upsert的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！