在 ON CONFLICT 子句中使用多冲突_target

我在表 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
....
134124 次浏览
小开

在今天看来是不可能的。ON CONFLICT 语法的最新版本既不允许重复这个子句，也不允许使用 慢性创伤性脑病: 不可能从 ON CONFLICT 中断 INSERT 以添加更多的冲突目标。

小开
  1. 创建约束(例如，外部索引)。

或/及

  1. 查看现有的约束(以 psquare 为单位的 d)。
  2. 在 INSERT 子句中使用 ONCONSTRINT (約束 _ name)。
小开

ON CONFLICT需要一个唯一的索引 * 来进行冲突检测。因此，您只需在两列上创建一个唯一的索引:

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) ，并且您希望允许重复的 id，但是不允许重叠的时间段。唯一约束不会帮助您，但是使用排除约束可以说“排除新记录，如果它们的 id等于旧的 id，而且它们的 valid_time与其 valid_time重叠”

小开
最佳答案

示例表和数据

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 _ expression 表达式，以及一个可选的 index _ 谓词 Table _ name 唯一索引，不管顺序如何，包含 准确地推断出了由  _ _ target 指定的列/表达式 (选择)作为仲裁索引 作为推理的进一步要求，必须满足仲裁者索引。

这给人的印象是，下面的查询应该可以工作，但实际上并非如此，因为它实际上需要 col1和 col2上的惟一索引。然而，这样的索引并不能保证 col1和 col2分别是唯一的，这是 OP 的要求之一。

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

让我们将这个查询称为 Q2(这将因语法错误而失败)

为什么？

Postgreql 之所以这样做，是因为在第二列上发生冲突时应该发生的事情没有得到很好的定义。有很多种可能性。例如在上面的 Q1查询中，当 col2上出现冲突时 postgreql 是否应该更新 col1？但是，如果这会导致 col1上的另一个冲突呢？后遗症怎么处理？

一个解决方案

一种解决方案是将 ON 冲突与 老式的 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;

您需要修改这个存储函数的逻辑，以便它按照您希望的方式更新列。就像这样

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

弗拉德的想法是对的。

首先你必须在列 col1, col2上创建一个表唯一约束，然后你可以做以下事情:

INSERT INTO dupes values(3,2,'c')
ON CONFLICT ON CONSTRAINT dupes_pkey
DO UPDATE SET col3 = 'c', col2 = 2
小开

如果使用 postgres 9.5，可以使用 EXCLUDED 空间。

以 PostgreSQL 9.5的新特性为例:

INSERT INTO user_logins (username, logins)
VALUES ('Naomi',1),('James',1)
ON CONFLICT (username)
DO UPDATE SET logins = user_logins.logins + EXCLUDED.logins;
小开

您通常可以(我认为)只使用一个 on conflict生成一个语句，该语句指定与所插入的内容相关的唯一约束。

因为通常情况下，一次只有一个约束是“相关的”约束。(如果有很多的话，那么我想知道是不是有些东西设计得很奇怪。)

例如:
(许可证: 没有 CC0，只有 CC-By)

// there're these unique constraints:
//   unique (site_id, people_id, page_id)
//   unique (site_id, people_id, pages_in_whole_site)
//   unique (site_id, people_id, pages_in_category_id)
// and only *one* of page-id, category-id, whole-site-true/false
// can be specified. So only one constraint is "active", at a time.


val thingColumnName = thingColumnName(notfificationPreference)


val insertStatement = s"""
insert into page_notf_prefs (
site_id,
people_id,
notf_level,
page_id,
pages_in_whole_site,
pages_in_category_id)
values (?, ?, ?, ?, ?, ?)
-- There can be only one on-conflict clause.
on conflict (site_id, people_id, $thingColumnName)   <—— look
do update set
notf_level = excluded.notf_level
"""


val values = List(
siteId.asAnyRef,
notfPref.peopleId.asAnyRef,
notfPref.notfLevel.toInt.asAnyRef,
// Only one of these is non-null:
notfPref.pageId.orNullVarchar,
if (notfPref.wholeSite) true.asAnyRef else NullBoolean,
notfPref.pagesInCategoryId.orNullInt)


runUpdateSingleRow(insertStatement, values)

还有:

private def thingColumnName(notfPref: PageNotfPref): String =
if (notfPref.pageId.isDefined)
"page_id"
else if (notfPref.pagesInCategoryId.isDefined)
"pages_in_category_id"
else if (notfPref.wholeSite)
"pages_in_whole_site"
else
die("TyE2ABK057")

on conflict子句是动态生成的，这取决于我要做的事情。如果要为页面插入通知首选项ーー那么在 site_id, people_id, page_id约束上可能存在唯一的冲突。如果我为一个类别配置通知首选项ーー那么我知道可能会违反的约束是 site_id, people_id, category_id。

所以我可以，而且很有可能你也可以？，生成正确的 on conflict (... columns )，因为我知道我 想要做什么，然后我知道哪一个单一的许多独特的约束，是一个可以得到违反。

小开

有点古怪，但我解决这个问题的方法是将 col1和 col2的两个值连接到一个新列 col3(有点像这两个值的索引) ，并与之进行比较。这只在您需要它同时匹配 col1和 col2时才能工作。

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

其中 col3 = col1和 col2值的串联。

小开

冲突是非常笨拙的解决方案，运行

UPDATE dupes SET key1=$1, key2=$2 where key3=$3
if rowcount > 0
INSERT dupes (key1, key2, key3) values ($1,$2,$3);

可以在 Oracle，Postgres 和所有其他数据库上工作

小开

我知道我迟到了，但对于那些寻找答案的人，我发现了这个: 给你

INSERT INTO tbl_Employee
VALUES (6,'Noor')
ON CONFLICT (EmpID,EmpName)
DO NOTHING;
小开 
ON CONFLICT ( col1, col2 )
DO UPDATE
SET

工作很好。但你 不应该更新 col1，col2在 SET部分。


提交答案