PostgreSQL表达式唯一索引无法被UPSERT的ON CONFLICT识别问题

报错原因 #

你触发报错的核心原因是：PostgreSQL的ON CONFLICT子句要求指定的冲突判断规则，必须和已存在的唯一索引/唯一约束的定义完全匹配。
你创建的唯一索引不是基于原始的column3、column4字段，而是基于COALESCE(column3, '0')、COALESCE(column4,'0')两个表达式，所以ON CONFLICT里直接写原始列名无法匹配到这个唯一索引，自然会抛出找不到对应约束的错误。

解决方法 #

方案1：修改ON CONFLICT子句匹配索引定义 #

直接把ON CONFLICT后的规则改成和你创建唯一索引时的表达式完全一致即可，示例代码如下：

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 *;

注意：COALESCE里使用的默认值类型必须和对应字段的类型完全匹配，比如如果column3是数值类型，默认值要写0而不是字符串'0'，否则依然会出现匹配失败的问题。

方案2（可选）：字段设默认值规避NULL #

如果业务允许，可以给column3、column4设置默认值（比如'0'或者0），同时禁止字段为NULL，之后就可以直接创建普通的多列唯一约束，ON CONFLICT直接写四个原始列名即可正常使用。 #

内容的提问来源于stack exchange，提问作者Etienne Savary