postgresql – 在ON CONFLICT子句中使用多个conflict_target

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');

重现问题

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

让我们来看一下Q1。结果是

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

documentation说什么

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的要求之一。

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

我们来调用这个查询Q2(这个失败，语法错误)

为什么？

Postgresql的行为是这样的，因为在第二列发生冲突时应该发生什么呢？有很多可能性。例如在上面的Q1查询中，当col2有冲突时，应该postgresql更新col1吗？但是如果这导致col1的另一个冲突呢？ postgresql如何预期处理？

一个办法

解决方案是将ON CONFLICT与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,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;

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

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