时间戳

首页 > 解决方案 > upsert 冲突目标中的 Where 子句

sql - upsert 冲突目标中的 Where 子句

问题描述

假设我有一个这样创建的表：

CREATE TABLE actions (name text primary key, value text);
INSERT INTO actions (name) VALUES ('bla');

尝试插入新行时，有三种可能性：

  1. 不存在“名称”行。应该插入新行。
  2. 存在值为 NULL 的“名称”行。应使用新值更新现有行。
  3. 存在具有非 NULL 值的“名称”行。插入应该失败。

我想我应该能够像这样使用 UPSERT 语法：

INSERT INTO actions (name, value) values ('bla', 'val1')
ON CONFLICT(name) WHERE value IS NULL
DO UPDATE SET value = excluded.value;

INSERT INTO actions (name, value) values ('bla', 'val2')
ON CONFLICT(name) WHERE value IS NULL
DO UPDATE SET value = excluded.value;

第一个命令按预期更新现有行。但是第二个命令再次更新了该行，这是我没想到的。

冲突目标中的 where 子句应该做什么？在我的测试中，它的评估结果是真还是假似乎并不重要。

基于带有 WHERE 子句的 PostgreSQL Upsert，我尝试使用唯一的部分索引而不是主键，但最终我得到了多个具有相同名称的行。

标签： sqlsqliteupsert

解决方案

使用第二个 WHERE将做你想要的：

INSERT INTO actions(name) VALUES ('bla');
INSERT INTO actions(name, value) VALUES ('bla', 'val1')
 ON CONFLICT(name) DO UPDATE SET value = excluded.value WHERE value IS NULL;
SELECT * FROM actions;
name        value
----------  ----------
bla         val1
INSERT INTO actions(name, value) VALUES ('bla', 'val2')
 ON CONFLICT(name) DO UPDATE SET value = excluded.value WHERE value IS NULL;
SELECT * FROM actions;
name        value
----------  ----------
bla         val1

第一个WHERE，在冲突列之后是为了选择一个特定的部分索引来检测冲突。一个给你感觉的例子：

sqlite> CREATE TABLE ex(name, value1, value2);
sqlite> CREATE UNIQUE INDEX ex_idx_name ON ex(name) WHERE value1 IS NULL;
sqlite> INSERT INTO ex(name) VALUES ('bla');
sqlite> INSERT INTO ex(name, value2) VALUES ('bla', 'val1');
Error: UNIQUE constraint failed: ex.name
sqlite> INSERT INTO ex(name, value1, value2) VALUES ('bla', 1, 'val2');
sqlite> SELECT * from ex;
name        value1      value2
----------  ----------  ----------
bla         (null)      (null)
bla         1           val2
sqlite> INSERT INTO ex(name, value2) VALUES ('bla', 'val3')
   ...> ON CONFLICT(name) WHERE value1 IS NULL DO UPDATE SET value2 = excluded.value2;
sqlite> SELECT * from ex;
name        value1      value2
----------  ----------  ----------
bla         (null)      val3
bla         1           val2
sqlite> INSERT INTO ex(name, value2) VALUES ('bla', 'val4')
   ...> ON CONFLICT(name) DO UPDATE SET value2 = excluded.value2;
Error: ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint

特别注意最后两个INSERT：第一个用空值更新行，第二个生成错误，因为它没有完全指定唯一索引。

推荐阅读