Skip to content
Advertisement

Postgres on conflict do update on composite primary keys

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

Advertisement

Answer

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)

Demo: db<>fiddle

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement