具有多个唯一约束的Postgres冲突处理 [英] Postgres conflict handling with multiple unique constraints

问题描述

我想对表使用在两行上具有唯一约束的conf。上的insert ..进行update ..语法。

I would like to use the insert.. on confict do update.. syntax with a table that has unique constraints on two columns. Is this possible?

例如mytable对col1和col2具有单独的唯一约束。

e.g. mytable has separate unique constraints on col1 and col2.

我可以写：

INSERT INTO mytable(col1, col2, col3) values ('A', 'B', 0) ON CONFLICT DO NOTHING;

但是这不起作用：

INSERT INTO mytable(col1, col2, col3) VALUES ('A', 'B', 0) 
ON CONFLICT 
DO UPDATE SET col3 = EXCLUDED.col3 + 1;

错误：在冲突时进行更新需要推理规范或约束名称

ERROR: ON CONFLICT DO UPDATE requires inference specification or constraint name

这也不起作用：

INSERT INTO mytable(col1, col2, col3) VALUES ('A', 'B', 0)
ON CONFLICT (col1, col2) 
DO UPDATE SET col3 = EXCLUDED.col3 + 1;

错误：没有符合ON CONFLICT规范的唯一或排除约束

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

此语法似乎是为针对两列的单个复合唯一约束而不是两个约束而设计的。

This syntax seems to be designed for a single composite unique constraint over two columns, rather than two constraints.

有什么方法可以做有条件更新，如果其中一个唯一约束被违反？这个问题如何在暗示它但不提供语法。

Is there any way to do a conditional update if either unique constraint is violated? This question How to upsert in Postgres on conflict on one of 2 columns? alludes to it but doesn't provide the syntax.

推荐答案

<$>当我们要求c $ c> ON CONFLICT 子句执行 DO UPDATE 时，它需要一个唯一的约束。定义主键后，仅引用列名就足够了；

The ON CONFLICT clause needs a single unique constraint when we ask it to DO UPDATE. When a primary key is defined, it is sufficient to just reference the column name; which is the dominant example one tends to find.

您提到您对col1和col2分别设置了唯一的约束，因此我可以假设您的表定义类似于

You mention that you have 'separate unique constraints on col1 and col2', so I might assume your table definition is similar to this:

CREATE TABLE mytable(       
    col1 varchar UNIQUE,    
    col2 varchar UNIQUE,    
    col3 int
);

但是您的查询引用的是复合约束；而不是单独的约束。修改后的表定义如下：

But your query is referencing a composite constraint; rather than separate constraints. A modified table definition like this:

CREATE TABLE mytable2(  
    col1 varchar UNIQUE,
    col2 varchar UNIQUE,
    col3 int,
    CONSTRAINT ux_col1_col2 UNIQUE (col1,col2)
);

可以与上面的查询一起使用：

would work with your query above:

INSERT INTO mytable(col1, col2, col3) VALUES ('A', 'B', 0)
ON CONFLICT (col1, col2) 
DO UPDATE SET col3 = EXCLUDED.col3 + 1;

您可以将此唯一约束引用为 ON CONFLICT（col1，col2） 或在冲突上ux_col1_col2 。

You can reference this unique constraint as either ON CONFLICT (col1, col2) or as ON CONFLICT ON CONSTRAINT ux_col1_col2.

但是，等一下更多...

该想法是使计数器列保持最新，以匹配
上的唯一列列，如果不存在则插入零...

The idea is to keep a counter column up to date which matches on either unique column, or insert zero if neither exists...

这与您在此处采用的路径不同。 matches on a unique unique column （允许在任一唯一列上进行匹配）可以同时匹配或不匹配。如果我了解您的意图，请仅贴一个标签，并在适用的记录上增加计数器。因此：

That's a different path than you're taking here. "matches on either unique column" allows for matching on both, either, or neither. If I understand your intent, just have a single label and increment the counters on the applicable records. So:

CREATE TABLE mytable2(  
    col1 varchar PRIMARY KEY,
    col3 int
);
INSERT INTO mytable2(col1,col3)
SELECT incr_label,0
FROM (VALUES ('A'),('B'),('C')) as increment_list(incr_label)
ON CONFLICT (col1)
DO UPDATE SET col3 = mytable2.col3 + 1
RETURNING col1,col3;

这篇关于具有多个唯一约束的Postgres冲突处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！