Postgres on conflict do update on composite primary keys

Question

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

Did you try ON CONFLICT ON CONSTRAINT variant? – kishore Jul 17 '18 at 07:40 — kishore, Jul 17 '18 at 07:40

Jim Jones · Accepted Answer · 2023-01-27T11:06:22.430

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') 
ON CONFLICT (person_id,question_id) 
DO UPDATE SET answer = EXCLUDED.answer || '-UPDATED';             

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

Demo: db<>fiddle

PostgreSQL 15 +

You can also achieve the same result using MERGE:

MERGE INTO answer i
  -- records to be inserted
  USING (
    VALUES (1,1,'q1'), -- already exists in table answers!
           (2,2,'q2')  -- new record
  ) AS j (person_id, question_id, answer)
  -- checking if the PK of given records (j) already exists
  -- in table "answer" (i).
  ON j.question_id = i.question_id AND j.person_id = i.person_id
WHEN MATCHED THEN
  -- in case of a match (conflict), I want to add the suffix '-UPDATED' 
  -- to the column "answer"
  UPDATE SET answer = j.answer || '-UPDATED'
WHEN NOT MATCHED THEN 
  -- if there is no match (conflict) just INSERT the record.
  INSERT (person_id, question_id, answer)
  VALUES (j.person_id, j.question_id, j.answer);

Demo: db<>fiddle

I have tried this. Currently testing on all cases. Since the documentation says that you need unique columns, I wasn't sure if this method covers all cases. Will get back to you after testing all cases. — pewpewlasers, Jul 19 '18 at 05:29
@pewpewlasers I believe, in this case the composite pk is considered as a single element and therefore a unique value. Please let me know in which cases it might not work. — Jim Jones, Jul 19 '18 at 09:11

score 16 · Answer 2 · answered Mar 07 '19 at 09:59

You could also define the primary externally to the table and then you don't need to re-write all columns included therein.

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

And then:

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

When the constraint changes in the future you don't need to manually adjust the insert statements to reflect this.

Postgres on conflict do update on composite primary keys

2 Answers2

PostgreSQL 15 +

Linked

Related