发生冲突的Postgres确实会更新复合主键

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

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

Jim Jones.. 13

只需将两个键放在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)

小智.. 6

您还可以在表的外部定义主数据库，然后无需重写其中包含的所有列。

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

ALTER TABLE "answer" ADD CONSTRAINT answer_pk PRIMARY KEY (person_id, question_id);

然后：

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

当约束将来发生变化时，您无需手动调整insert语句即可反映这一点。

只需将两个键放在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)

您还可以在表的外部定义主数据库，然后无需重写其中包含的所有列。

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

ALTER TABLE "answer" ADD CONSTRAINT answer_pk PRIMARY KEY (person_id, question_id);

然后：

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

当约束将来发生变化时，您无需手动调整insert语句即可反映这一点。