波斯格雷斯15

...添加子句NULLS NOT DISTINCT。您的案例现在可以开箱即用：

ALTER TABLE my_table
  DROP CONSTRAINT IF EXISTS ux_my_table_unique
, ADD CONSTRAINT ux_my_table_unique UNIQUE NULLS NOT DISTINCT (col2, col3);

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

参见：

• 创建具有空列的唯一约束

14岁或以上的Postgres

NULL值被视为彼此不相等，因此永远不会触发UNIQUE违规。这意味着当前表定义未执行您要求它执行的操作。可能已经存在多个具有(col2, col3) = (1, NULL)的行。在当前设置中，ON CONFLICT永远不会为col3 IS NULL触发。
您可以使用两个部分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 NOTHING变体才能用作"捕获所有"。请参见：
• 如何在PostgreSQL中使用RETURNING和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;  -- ②

• db〈〉小提琴here *

在任何情况下都有效。
even适用于col3的NULL和NOT NULL值任意混合的多个输入行。
而且甚至不会比普通语句花费更多，因为每行只进入两个UPSERT中的一个。
这是那些"Eurika!"查询中的一个，所有的东西都会点击，排除万难。：）
①注意CTE input中的::real的显式强制转换。以下相关答案解释了原因：

• 更新多行时转换NULL类型

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

• 如何（或可以）对多列执行SELECT DISTINCT操作？

如果你能找到一个永远不可能合法存在于col3中的值（确保有一个check约束），你可以使用一个唯一索引：

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;