PostgreSQL中带列别名的ON CONFLICT DO UPDATE不生效问题

以下是针对你的问题的具体排查和解决方向：

1. 确认唯一约束的有效性 #

虽然你提到列上有唯一约束，但需确保约束是单独作用于"ITEM_CODE(material_code)"列，而非包含该列的复合约束。执行以下查询验证约束定义：

SELECT conname, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'api_itemmaster'::regclass
  AND contype = 'u';

如果输出的约束定义是类似"ITEM_CODE(material_code)" UNIQUE则正常；若为复合约束（如("ITEM_CODE(material_code)", other_column) UNIQUE），则ON CONFLICT子句需要指定完整的约束名或所有参与约束的列。

2. 检查冲突值的完全匹配性 #

字符串类型的列容易因大小写、前后空格、隐藏字符导致匹配失败。执行以下查询验证现有行的实际值：

-- 精确匹配查询
SELECT "ITEM_CODE(material_code)" FROM api_itemmaster WHERE "ITEM_CODE(material_code)" = '5000';

-- 排查隐藏字符或长度差异
SELECT "ITEM_CODE(material_code)", octet_length("ITEM_CODE(material_code)") 
FROM api_itemmaster WHERE "ITEM_CODE(material_code)" LIKE '%5000%';

如果精确匹配无结果，说明插入值与现有行的值存在细微差异，需修正插入值或清理现有数据。

3. 排查触发器/规则的干扰 #

表上的BEFORE INSERT或BEFORE UPDATE触发器可能修改插入值，导致冲突未触发或更新被覆盖。在测试环境下临时禁用触发器验证：

ALTER TABLE api_itemmaster DISABLE TRIGGER ALL;
-- 执行你的INSERT语句
ALTER TABLE api_itemmaster ENABLE TRIGGER ALL;

若禁用后更新生效，需检查触发器逻辑是否存在异常处理逻辑。

4. 验证EXCLUDED表的字段引用 #

确保EXCLUDED后的列名与插入列完全一致，包括引号和别名格式。例如EXCLUDED."ITEM_NAME(material_name)"必须和INSERT列表中的列名完全匹配，不能遗漏引号或写错别名。

5. 排查事务并发问题 #

高并发场景下，事务隔离级别可能导致当前事务无法感知到未提交的冲突行。尝试在显式事务中执行并验证：

BEGIN;
-- 执行你的INSERT语句
SELECT * FROM api_itemmaster WHERE "ITEM_CODE(material_code)" = '5000';
COMMIT;

查看提交后的数据是否已更新，若在事务内可见更新但提交后失效，需排查并发事务的冲突处理逻辑。

内容的提问来源于stack exchange，提问作者Ravi Choudhary