Postgres on conflict do update on composite primary keys
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
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.