PostgreSQL中带SHA256哈希唯一索引的PostGIS表INSERT ON CONFLICT语法问题

嗨，我来帮你搞定这个语法问题！你之所以遇到报错，是因为ON CONFLICT ON CONSTRAINT这个语法是用来指定表级唯一约束的，但你创建的是一个基于表达式的唯一索引，两者在PostgreSQL里的识别逻辑不一样，所以数据库找不到对应的约束名。

给你两种可行的解决办法，选哪种看你的需求：

方法一：直接匹配索引的表达式（无需修改表结构） #

既然你的唯一索引是基于SHA256(ST_Normalize(geom))这个表达式创建的，那在ON CONFLICT里直接写这个表达式就行，PostgreSQL会自动匹配到对应的唯一索引：

INSERT INTO environment.fires (geom, -- 替换成你实际要插入的其他列
                               column1,
                               column2)
VALUES (ST_GeomFromText('POINT(...)'), -- 替换成你的空间数据
        'value1',
        'value2')
ON CONFLICT (SHA256(ST_Normalize(geom))) DO NOTHING;

方法二：把索引转换成唯一约束（适合需要固定约束名的场景） #

如果更倾向于用约束名来操作，你可以把现有的唯一索引替换成唯一约束（PostgreSQL里唯一约束本质也是唯一索引，但会被标记为约束，能被ON CONFLICT ON CONSTRAINT识别）：

首先删掉原来的唯一索引：

DROP INDEX IF EXISTS environment.idx_uniquegeometry_geom;

然后添加唯一约束：

ALTER TABLE environment.fires
ADD CONSTRAINT idx_uniquegeometry_geom UNIQUE (SHA256(ST_Normalize(geom)));

之后你的INSERT语句就可以用约束名来写了：

INSERT INTO environment.fires (geom, column1, column2)
VALUES (ST_GeomFromText('POINT(...)'), 'value1', 'value2')
ON CONFLICT ON CONSTRAINT idx_uniquegeometry_geom DO NOTHING;

简单总结下：方法一更灵活，不用动表结构；方法二能让约束名更明确，后续维护起来更清晰，根据你的实际场景选就行～

备注：内容来源于stack exchange，提问作者HelpOverFlow