基于NULL值的唯一约束的UPSERT

Question

我有一个Postgres表，在多个列上有一个唯一的约束，其中一个可以为NULL。对于每个组合，我只希望允许该列中有一条空记录。

create table my_table (
   col1 int generated by default as identity primary key,
   col2 int not null,
   col3 real,
   col4 int,
   constraint ux_my_table_unique unique (col2, col3)
);

我有一个upsert查询，当它遇到col2、col3中具有相同值的记录时，我想更新col4：

insert into my_table (col2, col3, col4) values (p_col2, p_col3, p_col4)
on conflict (col2, col3) do update set col4=excluded.col4;

但是当col3为空时，冲突不会触发。我读过关于使用触发器的文章。请问让冲突触发的最佳解决方案是什么？

Answer 1

NULL值不被视为彼此相等，因此永远不会触发UniQUE冲突。这意味着，您当前的表定义没有执行您所说的应该执行的操作。已经可以有多行（col2， col3）=（1， NULL）。ON CONFLICT永远不会在您当前的设置中为col3 IS NULL触发。

正在开发一个用于UNIQUE约束的UNIQUE NULLS[NOT]DISTINCT选项。但这最多只能在未来的15年后进行。

您可以使用两个部分的UNIQUE索引强制执行UNIQUE约束，如下所示：

• 使用空列创建唯一约束

适用于您的案例：

CREATE UNIQUE INDEX my_table_col2_uni_idx ON my_table (col2)
WHERE col3 IS NULL;

CREATE UNIQUE INDEX my_table_col2_col3_uni_idx ON my_table (col2, col3)
WHERE col3 IS NOT NULL;

但是ON CONFLICT... DO UPDATE只能基于一个单一的UniQUE索引或约束。只有ON CONFLICT DO NOTH变体可以作为捕获一切。请参见：

• 如何在PostgreSQL中使用返回与ON CONFLICT

你现在想要的似乎是不可能的。。。

不过，有一个完美的解决方案。有了两个部分UNIQUE索引，就可以根据col3的输入值使用正确的语句：

WITH input(col2, col3, col4) AS (
   VALUES
     (3, NULL::real, 5)  -- ①
   , (3, 4, 5)
   )
, upsert1 AS (
   INSERT INTO my_table AS t(col2, col3, col4)
   SELECT * FROM input          WHERE col3 IS NOT NULL
   ON     CONFLICT (col2, col3) WHERE col3 IS NOT NULL  -- matching index_predicate!
   DO     UPDATE
   SET    col4 = EXCLUDED.col4
   WHERE  t.col4 IS DISTINCT FROM EXCLUDED.col4  -- ②
   )
INSERT INTO my_table AS t(col2, col3, col4)
SELECT * FROM input    WHERE col3 IS NULL
ON     CONFLICT (col2) WHERE col3 IS NULL  -- matching index_predicate!
DO     UPDATE SET col4 = EXCLUDED.col4
WHERE  t.col4 IS DISTINCT FROM EXCLUDED.col4;  -- ②

分贝

在任何情况下都有效
甚至适用于多个输入行，任意混合使用NULL和NOT NULL的col3值
而且成本甚至不会比普通语句高很多，因为每一行只进入两个上游中的一个。

这是其中一个“Eurika！”尽管困难重重，但一切都只是点击的查询。：）

①注意CTE输入中对::real的显式转换。这个相关的答案解释了为什么：

• 更新多行时转换为NULL类型

②最后的WHERE子句是可选的，但强烈推荐使用。如果UPDATE没有实际改变任何内容，那将是一种浪费。请参见：

• 如何在多个列上选择DISTINCT？

Answer 2

如果您可以在col3中找到一个永远无法合法存在的值（请确保带有检查约束），则可以使用唯一索引：

CREATE UNIQUE INDEX ON my_table (
   col2,
   coalesce(col3, -1.0)
);

并在INSERT中使用它：

INSERT INTO my_table (col2, col3, col4)
VALUES (p_col2, p_col3, p_col4)
ON CONFLICT (col2, coalesce(col3, -1.0))
DO UPDATE SET col4 = excluded.col4;