发生冲突的Postgres确实会在复合主键上进行更新 [英] Postgres on conflict do update on composite primary keys

问题描述

我有一张桌子，用户可以在其中回答问题。规则是用户可以回答许多问题，或者许多用户可以回答一个问题，但用户只能回答一次特定问题。如果用户再次回答该问题，则只需替换旧的问题即可。通常，当我们处理唯一列时，on冲突确实会更新。在这种情况下，列 person_id 和 question_id 不能唯一。但是，两者的结合始终是唯一的。如何实现在冲突时会更新的插入语句？

I have a table where a user answers to a question. The rules are that the user can answer to many questions or many users can answer one question BUT a user can answer to a particular question only once. If the user answers to the question again, it should simply replace the old one. Generally the on conflict do update works when we are dealing with unique columns. In this scenario the columns person_id and question_id cannot be unique. However the combination of the two is always unique. How do I implement the insert statement that does update on conflict?

CREATE TABLE "answer" (
  "person_id" integer NOT NULL REFERENCES person(id), 
  "question_id" integer NOT NULL REFERENCES question(id) ON DELETE CASCADE, /* INDEXED */
  "answer" character varying (1200) NULL,
  PRIMARY KEY (person_id, question_id) 
);

推荐答案

只需将两个键都放在 ON CONFLICT 子句：

Just place both keys in the ON CONFLICT clause:

INSERT INTO answer VALUES (1,1,'q1') 
ON CONFLICT (person_id,question_id) 
DO UPDATE SET answer = EXCLUDED.answer; 

示例：

INSERT INTO answer VALUES (1,1,'q1') 
ON CONFLICT (person_id,question_id) 
DO UPDATE SET answer = EXCLUDED.answer;             

SELECT * FROM answer;
 person_id | question_id | answer 
-----------+-------------+--------
         1 |           1 | q1
(1 Zeile)

INSERT INTO answer VALUES (1,1,'q1-UPDATED') 
ON CONFLICT (person_id,question_id) 
DO UPDATE SET answer = EXCLUDED.answer;             

SELECT * FROM answer;
 person_id | question_id |   answer   
-----------+-------------+------------
         1 |           1 | q1-UPDATED
(1 Zeile)

这篇关于发生冲突的Postgres确实会在复合主键上进行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！