无法识别UPSERT表达式的唯一索引

Question

我这样做是为了解决PostgreSQL中唯一约束的两列中有空值的问题：

CREATE UNIQUE INDEX uc_column1_column2_column3_column4 ON schema.table
(column1, column2, COALESCE(column3, '0'), COALESCE(column4,'0'));

请参见此处：使用空列创建唯一约束

但现在我有另一个问题，因为使用索引而不是约束，我有一个错误：

不存在与冲突规范匹配的唯一或排除约束

当我这样做SQL要求：

INSERT INTO table
        (column1,column2, column3, column4, columnA, columnB, columnC) 
        VALUES (1,2,3,4,A,B,C)
ON CONFLICT (column1,column2, column3, column4) 
DO UPDATE SET column1= EXCLUDED.column1
RETURNING *

注意：我确实得到了正确的约束错误，没有“on conflict”行。

Answer 1

如果带有合并（column3，'0'）的表达式索引不会导致NULL和0之间的冲突，那么您最好首先将column3定义为column3 integer not NULL默认值0（猜测数据类型）。与第4列相同。这将消除所有的并发症。

当你（必须）使用该表达式索引时，你的“冲突目标”必须与唯一索引的定义相匹配，就像塞廷建议的那样：

INSERT INTO table
       (column1, column2, column3, column4, columnA, columnB, columnC) 
VALUES (1, 2, 3, 4, A, B, C)
ON     CONFLICT (column1, column2, COALESCE(column3, '0'), COALESCE(column4,'0')) DO UPDATE
SET    column1 = EXCLUDED.column1
RETURNING *;

由于您使用的是唯一索引，而不是唯一约束，因此您不能只提供约束名称，必须走“唯一索引推断”的路线。或者，正如手册所说：

conflict_target可以执行唯一索引推断。当执行推断时，它由一个或多个index_column_name列和/或index_expression表达式以及一个可选的index_predicate组成。所有的table_name唯一索引，如果不考虑顺序，恰好包含指定的conflict_target列/表达式，则推断（选择）为仲裁索引。

旁边：

DO UPDATE SET column1=已排除。column1从不根据定义更改任何内容。在编写新的行版本时，它的成本仍然很高。可能用于始终返回一行。通常，有更便宜的解决方案。见：

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