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

演示：db<>fiddle

PostgreSQL 15+版本

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

演示：db<>fiddle

您还可以在表的外部定义主列，这样就不需要重写其中包含的所有列。

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语句来反映这一点。