在 ON CONFLICT 子句中使用多个冲突目标 [英] Use multiple conflict_target in ON CONFLICT clause

问题描述

我在表 col1、col2 中有两列，它们都是唯一索引(col1 是唯一的，col2 也是唯一的).

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

它有效:

INSERT INTO 表...冲突 ( col1 )做更新放-- 在此处更新所需的列

但是如何对多列执行此操作，如下所示:

<代码>...冲突 ( col1, col2 )做更新放....

解决方案

示例表和数据

CREATE TABLE dupes(col1 int 主键，col2 int，col3 文本，约束 col2_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.结果是

错误:重复键值违反唯一约束col2_unique"详细信息:键 (col2)=(2) 已经存在.

文档 的内容

<块引用>

conflict_target 可以执行唯一索引推断.表演时推断，它由一个或多个 index_column_name 列和/或index_expression 表达式和一个可选的 index_predicate.全部table_name 唯一索引，不考虑顺序，包含确切地推断出conflict_target 指定的列/表达式(选择)作为仲裁索引.如果指定了 index_predicate，则它作为推理的进一步要求，必须满足仲裁索引.

这给人的印象是下面的查询应该可以工作，但事实并非如此，因为它实际上需要 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 如何处理这个问题?

解决方案

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

CREATE OR REPLACE FUNCTION merge_db(key1 INT, key2 INT, data TEXT) RETURNS VOID AS$$开始环形-- 首先尝试更新密钥UPDATE dupes SET col3 = data WHERE col1 = key1 and col2 = key2;如果找到 THEN返回;万一;-- 不存在，所以尝试插入密钥-- 如果其他人同时插入相同的密钥，或 key2-- 已经存在于 col2，-- 我们可能会遇到唯一键失败开始INSERT INTO dupes VALUES (key1, key2, data) ON CONFLICT (col1) DO UPDATE SET col3 = data;返回;EXCEPTION WHEN unique_violation THEN开始INSERT INTO dupes VALUES (key1, key2, data) ON CONFLICT (col2) DO UPDATE SET col3 = data;返回;EXCEPTION WHEN unique_violation THEN-- 什么都不做，然后循环再次尝试更新.结尾;结尾;结束循环；结尾;$$语言 plpgsql;

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

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

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

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.

It works:

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 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.

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)

Why?

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?

A solution

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 子句中使用多个冲突目标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！