在ON CONFLICT子句中使用多个flict_target [英] Use multiple conflict_target in ON CONFLICT clause

问题描述

我在表 col1 ， col2 中有两列，它们都是唯一索引的（col1是唯一的，因此是col2）。

I have two columns in table col1, col2, they both are unique indexed (col1 is unique and so is col2).

我需要在插入此表时，使用 ON CONFLICT 语法并更新其他列，但是我不能在 conflict_target 条款中使用这两列。

I need at insert into this table, use ON CONFLICT syntax and update other columns, but I can't use both column in conflict_targetclause.

它起作用：

INSERT INTO table
...
ON CONFLICT ( col1 ) 
DO UPDATE 
SET 
-- update needed columns here

但是如何针对多个列执行此操作，例如：

But how to do this for several columns, something like this:

...
ON CONFLICT ( col1, col2 )
DO UPDATE 
SET 
....

推荐答案

示例表和数据

A sample table and data

CREATE TABLE dupes(col1 int primary key, col2 int, col3 text,
   CONSTRAINT col2_unique UNIQUE (col2)
);

INSERT INTO dupes values(1,1,'a'),(2,2,'b');

重现问题

Reproducing the problem

INSERT INTO dupes values(3,2,'c')
ON CONFLICT (col1) DO UPDATE SET col3 = 'c', col2 = 2

我们将此称为第一季度。结果是

Let's call this Q1. The result is

ERROR:  duplicate key value violates unique constraint "col2_unique"
DETAIL:  Key (col2)=(2) already exists.

文档说

What the documentation says

conflict_target可以执行唯一的索引推断。执行
推断时，它由一个或多个index_column_name列和/或
index_expression表达式以及一个可选的index_predicate组成。所有
table_name唯一索引（不考虑顺序）都包含
，这些索引恰好由flict_target指定的列/表达式被推导为选定仲裁者索引
（选择）。如果指定了index_predicate，则作为推论的进一步要求，它的
必须满足仲裁者索引。

conflict_target can perform unique index inference. When performing inference, it consists of one or more index_column_name columns and/or index_expression expressions, and an optional index_predicate. All table_name unique indexes that, without regard to order, contain exactly the conflict_target-specified columns/expressions are inferred (chosen) as arbiter indexes. If an index_predicate is specified, it must, as a further requirement for inference, satisfy arbiter indexes.

认为下面的查询应该起作用，但这不是因为它实际上需要col1和col2在一起的唯一索引。但是，这样的索引不能保证col1和col2分别唯一，这是OP的要求之一。

This gives the impression that the following query should work, but it does not because it would actually require a together unique index on col1 and col2. However such an index would not guarantee that col1 and col2 would be unique individually which is one of the OP's requirements.

INSERT INTO dupes values(3,2,'c') 
ON CONFLICT (col1,col2) DO UPDATE SET col3 = 'c', col2 = 2

让我们将此查询称为第二季度（此操作因语法错误而失败）

Let's call this query Q2 (this fails with a syntax error)

Postgresql的行为是因为未明确定义第二列发生冲突时应发生的情况。有很多可能性。例如，在上面的Q1查询中，当 col2 发生冲突时，PostgreSQL应该更新 col1 吗？但是，如果这导致在 col1 上发生另一个冲突怎么办？

Postgresql behaves this way is because what should happen when a conflict occurs on the second column is not well defined. There are number of possibilities. For example in the above Q1 query, should postgresql update col1 when there is a conflict on col2? But what if that leads to another conflict on col1? how is postgresql expected to handle that?

一个解决方案是将ON CONFLICT与老式的UPSERT 。

A solution is to combine ON CONFLICT with old fashioned UPSERT.

CREATE OR REPLACE FUNCTION merge_db(key1 INT, key2 INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        UPDATE dupes SET col3 = data WHERE col1 = key1 and col2 = key2;
        IF found THEN
            RETURN;
        END IF;

        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently, or key2
        -- already exists in col2,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO dupes VALUES (key1, key2, data) ON CONFLICT (col1) DO UPDATE SET col3 = data;
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            BEGIN
                INSERT INTO dupes VALUES (key1, key2, data) ON CONFLICT (col2) DO UPDATE SET col3 = data;
                RETURN;
            EXCEPTION WHEN unique_violation THEN
                -- Do nothing, and loop to try the UPDATE again.
            END;
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

您需要修改此存储函数的逻辑，以便它完全按照您的方式更新列想要它。像这样调用

You would need to modify the logic of this stored function so that it updates the columns exactly the way you want it to. Invoke it like

SELECT merge_db(3,2,'c');
SELECT merge_db(1,2,'d');

这篇关于在ON CONFLICT子句中使用多个flict_target的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！