我有一个用户回答问题的表格.规则是，用户可以回答许多问题，或者许多用户可以回答一个问题，但一个用户只能回答一个特定问题.如果用户再次回答这个问题，它应该简单地替换原来的问题.通常情况下，当我们处理唯一列时，on conflict do update会起作用.在这种情况下，列person_id和question_id不能是唯一的.然而，两者的结合总是独一无二的.如何实现insert语句，该语句会根据冲突进行更新？
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子句中:
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)
演示:db<>fiddle