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

How to correctly do upsert in postgres 9.5

提问by user1575921

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

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 (, , , )
  ON CONFLICT (category_id)
  DO UPDATE SET
  category_id = ,
  last_modified_date = ,
  last_modified_by_user_id = 
  WHERE gallery_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 uniquebecuase I want to sure both column are same then do update....

我尝试更改WHERE gallery_id = $2;为 WHERE category_gallery.gallery_id = $2;then 显示错误there is no unique or exclusion constraint matching the ON CONFLICT specification，但我不想将 gallery_id 或 category_id 设置为唯一，因为我想确保两列相同然后进行更新....

How to correctly do upsert in postgres 9.5?

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

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

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

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 (, , , )
  ON CONFLICT (category_id, gallery_id)
  DO UPDATE SET
  category_id = ,
  last_modified_date = ,
  last_modified_by_user_id = 
  WHERE gallery_id = `;


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

table (category_id , gallery_id not unique column)

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

回答by ypercube??

The ON CONFLICTconstruct requires a UNIQUEconstraint to work. From the documentation on INSERT .. ON CONFLICTclause:

该ON CONFLICT构造需要一个UNIQUE约束才能工作。从关于INSERT .. ON CONFLICT条款的文件：

The optional ON CONFLICTclause specifies an alternative action to raising a unique violationor exclusion constraintviolation 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 NOTHINGsimply avoids inserting a row as its alternative action. ON CONFLICT DO UPDATEupdates the existing row that conflicts with the row proposed for insertion as its alternative action.

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

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

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

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

If the row to be inserted matches bothvalues with a row already on the table, then instead of INSERT, do an UPDATE:

如果要插入的行与表中已有的行匹配这两个值，则INSERT执行以下操作UPDATE：

INSERT INTO category_gallery (
  category_id, gallery_id, create_date, create_by_user_id
  ) VALUES (, , , )
  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 ;

You can use either the columns of the UNIQUE constraint:

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

  ON CONFLICT (category_id, gallery_id) 

or the constraint name:

或约束名称：

  ON CONFLICT ON CONSTRAINT category_gallery_uq  

回答by Erik

As as simplified alternative to the currently accepted answer, the UNIQUEconstraint can be anonymously added upon creation of the table:

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

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

Then, the upsert query becomes (similar to what was already answered):

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

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