如何在2列之一上的冲突中向Postgres增补？ [英] How to upsert in Postgres on conflict on one of 2 columns?

问题描述

当表中的两列之一发生冲突时，是否可以在Postgres 9.5中进行 upsert ？基本上，我有2列，如果任何一列引发唯一约束违例，那么我想执行更新操作。

Is it possible to do upsert in Postgres 9.5 when conflict happens on one of 2 columns in a table.? Basically I have 2 columns and if either column throws unique constraint violation, then I would like to perform update operation.

推荐答案

是的，此行为是默认行为。如果指定了 ON CONFLICT DO UPDATE ，则任何唯一约束冲突都构成冲突，然后执行 UPDATE 。 INSERT 语句只能有一个 ON CONFLICT 子句，但 conflict_target 可以指定多个列名称，每个列名称都必须具有索引，例如 UNIQUE 约束。但是，您仅限于单个 conflict_action ，并且在处理该操作时，您将不会获得有关哪个约束导致冲突的信息。如果需要此类信息或根据违反约束的情况采取特定操作，则应编写触发函数，但会丢失 INSERT ...发生冲突时的所有重要原子性... 语句。

Yes, and this behaviour is default. Any unique constraint violation constitutes a conflict and then the UPDATE is performed if ON CONFLICT DO UPDATE is specified. The INSERT statement can have only a single ON CONFLICT clause, but the conflict_target of that clause can specify multiple column names each of which must have an index, such as a UNIQUE constraint. You are, however, limited to a single conflict_action and you will not have information on which constraint caused the conflict when processing that action. If you need that kind of information, or specific action depending on the constraint violation, you should write a trigger function but then you lose the all-important atomicity of the INSERT ... ON CONFLICT DO ... statement.

这篇关于如何在2列之一上的冲突中向Postgres增补？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！