PostgreSQL含json字段临时表插入主表报相等运算符错误解决

PostgreSQL 原生json类型是文本格式存储，没有内置等值比较运算符——毕竟语义完全相同的JSON数据，可能因为键顺序、多余空格的差异，存储的文本内容完全不同，官方没有默认提供武断的等值判断逻辑，避免返回不符合预期的结果。
你的两个查询都触发了需要对values列做等值判断的逻辑：第一个查询的DISTINCT *会对所有返回列（含json类型的values）做重复值校验；第二个查询不仅源表和目标表写反、逻辑完全错误，关联判断时也触发了字段校验，最终抛出could not identify an equality operator for type json错误。

方案1：长期最优解，改用jsonb类型 #

jsonb是PostgreSQL提供的二进制结构化JSON存储类型，会自动去掉无意义空格、统一键顺序，原生支持等值判断、索引、包含查询等各类能力，查询性能远高于文本存储的json类型。如果表结构可调整，直接修改两个表的values字段类型即可一劳永逸：

-- 修改主表字段类型
ALTER TABLE main_table ALTER COLUMN "values" TYPE jsonb USING "values"::jsonb;
-- 修改临时表字段类型
ALTER TABLE temp ALTER COLUMN "values" TYPE jsonb USING "values"::jsonb;

注意values是SQL保留字，写查询时建议给字段名加双引号，避免触发语法歧义。改完字段类型后，基于md5sum的冲突判断、去重逻辑都可以正常运行。

方案2：不修改表结构，调整查询逻辑 #

如果暂时无法调整字段类型，可以直接优化查询逻辑，从根源上避免触发json类型的等值判断：

• 你已经设置md5sum作为唯一冲突键，重复判断的唯一依据就是md5sum值，去重、关联时完全不需要对values列做比较，也就不会触发json类型的等值校验
• 修正原第二个查询的逻辑错误：原写法是从主表读数据再插回主表，完全颠倒了“临时表数据插入主表”的逻辑，正确逻辑应该是从临时表读数据，判断md5sum不存在于主表时才插入

修正后的ON CONFLICT写法（推荐，语法更简洁）：

INSERT INTO main_table (md5sum, "values")
SELECT DISTINCT ON (md5sum) md5sum, "values"
FROM temp
ON CONFLICT (md5sum) DO NOTHING;

这里用PG内置的DISTINCT ON (md5sum)语法，只会按md5sum去重，同md5sum的记录只取第一条，不会碰values列的类型判断，效率比全字段DISTINCT高很多。

修正后的NOT EXISTS写法：

INSERT INTO main_table(md5sum, "values")
SELECT DISTINCT ON (md5sum) md5sum, "values"
FROM temp
WHERE NOT EXISTS (
    SELECT 1
    FROM main_table
    WHERE temp.md5sum = main_table.md5sum
);

这里NOT EXISTS子查询只写SELECT 1即可，不需要返回具体字段，关联条件也只判断md5sum，完全不会触发json类型的等值错误。

如果你确实需要同时判断JSON内容完全一致才认定为重复，又不想改字段类型，只需要在去重时将json字段强转为jsonb做判断即可：

INSERT INTO main_table (md5sum, "values")
SELECT DISTINCT ON (md5sum, "values"::jsonb) md5sum, "values"
FROM temp
ON CONFLICT (md5sum) DO NOTHING;

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