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

前端之家收集整理的这篇文章主要介绍了postgresql – 在ON CONFLICT子句中使用多个conflict_target前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我在表col1,col2中有两列,它们都是唯一的索引(col1是唯一的,所以是col2)。

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

有用:

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

但是如何做到这几个列,像这样:

...
ON CONFLICT ( col1,col2 )
DO UPDATE 
SET 
....
样本表和数据
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');
原文链接:https://www.f2er.com/postgresql/193248.html

猜你在找的Postgre SQL相关文章