使用自动增量Postgres插入/更新冲突

Question

我有一个答案表，它有answers.id，answers.question_id，answers.value和answers.report_id

answers.id在常规插入时自动递增。

answers.question_id的值介于1到10之间，因为有10个问题，但对表来说不是唯一的，并且依赖于report_id列。

answers.report_id用于引用单独表中的报表。

我要实现的是，如果对于特定的report_id没有冲突的questions_id，则插入新的记录。如果有冲突，则用新的值更新记录

到目前为止，我已经读到了UPSERT

我尝试了以下查询

INSERT INTO answers (id, value) 
VALUES(8, 3) 
ON CONFLICT (id) DO UPDATE SET value = EXCLUDED.value
WHERE answers.report_id = 55

查询工作正常，但问题是每次没有冲突时，我都必须为id分配一个唯一的值，我不知道下一个最好的id是什么。

我的另一个想法是创建第二列，在其中存储另一个唯一的id，用于答案和冲突检查，并将id保持为自动增量。

我的第三个想法是，首先为特定的报告运行问题id的选择问题。如果没有这样的结果，请运行常规插入，否则请更新现有插入。。。

理想情况下，我希望运行单个查询，而不是创建第二个唯一答案id列。




Answer 1

在我看来，您应该用两列创建一个唯一的约束：

ALTER TABLE answers ADD UNIQUE (report_id, question_id);

然后，您可以将该约束与冲突上的一起使用：

INSERT INTO answers (question_id, report_id, value)
VALUES (1, 2, 'gewonnen')
ON CONFLICT (report_id, question_id) DO UPDATE
SET value = EXCLUDED.value;

请注意，您不需要WHERE子句，因为这只与冲突行有关。

还要注意，我没有指定id，所以使用了自动生成的值。