Postgres on conflict do update on composite primary keys

pewpewlasers picture pewpewlasers · Jul 17, 2018 · Viewed 13.6k times · Source

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

Answer

Jim Jones picture Jim Jones · Jul 17, 2018

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; 

Example:

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)