在ON conflict子句中使用多个conflict_target

Question

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

我需要在插入到这个表，使用ON CONFLICT语法和更新其他列，但我不能在conflict_target子句中使用这两个列。

它的工作原理：

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

但是如何在几个列中做到这一点，就像这样：

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

Answer 1

在今天看来这是不可能的。ON CONFLICT语法的最新版本既不允许重复该子句，也不允许使用CTE：不可能从ON CONFLICT中重新插入以添加更多冲突目标。

Answer 2

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,2,'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.

冲突_目标可以执行唯一索引推断。在执行推理时，它由一个或多个index_column_name列和/或index_表达式以及可选的index_谓词组成。所有表名唯一索引（不考虑顺序）都会被推断（选择）为仲裁器索引，这些索引恰好包含冲突目标指定的列/表达式。如果指定了索引_谓词，作为推理的进一步要求，它必须满足仲裁器索引。

这给人的印象是，下面的查询应该可以工作，但它不能工作，因为它实际上需要在col1和col2上同时使用唯一索引。然而，这样的索引不能保证col1和col2单独是唯一的，这是OP的要求之一。

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

让我们将此查询称为Q2（由于语法错误而失败）

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

一个解决方案是将冲突与老式的升级相结合。

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;

您需要修改这个存储函数的逻辑，以便它能以您希望的方式完全更新列。像这样调用它

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

Answer 3

冲突上的需要一个唯一的索引*来进行冲突检测。所以你只需要在两列上创建一个唯一的索引：

t=# create table t (id integer, a text, b text);
CREATE TABLE
t=# create unique index idx_t_id_a on t (id, a);
CREATE INDEX
t=# insert into t values (1, 'a', 'foo');
INSERT 0 1
t=# insert into t values (1, 'a', 'bar') on conflict (id, a) do update set b = 'bar';
INSERT 0 1
t=# select * from t;
 id | a |  b  
----+---+-----
  1 | a | bar

*除了唯一索引外，还可以使用排除约束。这些约束比唯一约束更一般。假设您的表中有id和valid_-time（并且valid_-time是一个tsrange），您希望允许重复的ids，但不允许重叠的时间段。唯一的约束对您没有帮助，但使用排除约束时，您可以说“如果新记录的id等于旧的id，并且它们的有效时间与其有效时间重叠，则排除新记录”