PostgreSQL执行UPDATE报错：无法识别json类型的相等运算符

执行以下PostgreSQL UPDATE语句时，触发错误：
ERROR: could not identify an equality operator for type json

对应的SQL语句：

update pdfs
set forced_payload = datatable.forced_payload,
    s3_original_path = datatable.s3_original_path,
    s3_image_path = datatable.s3_image_path,
    s3_preview_path = datatable.s3_preview_path,
    s3_original_inbox_path = datatable.s3_original_inbox_path,
    s3_preview_inbox_path = datatable.s3_preview_inbox_path
from (select 43238::integer as id,
             NULL::json as forced_payload,
             NULL::character varying(255) as s3_original_path,
             NULL::character varying(255) as s3_image_path,
             NULL::character varying(255) as s3_preview_path,
             NULL::character varying(255) as s3_original_inbox_path,
             NULL::character varying(255) as s3_preview_inbox_path
      union
      select 43239,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL
     ) as datatable
where pdfs.id = datatable.id;

该语句由Rails项目的bulk_data_methods gem生成，此前同类报错多关联DISTINCT或GROUP_BY，但本次场景不涉及，以下是错误原因及解决办法：

错误原因 #

PostgreSQL的json类型没有内置的相等比较运算符。你的语句中使用了UNION，UNION的逻辑是合并结果集并自动去重，这就需要对所有列执行相等性校验——哪怕forced_payload列的值是NULL，PostgreSQL依然会检查该类型是否支持相等比较，因此触发了这个错误。

解决办法 #

方法一：用UNION ALL替代UNION #

如果你的子查询本来就不会产生重复行（比如两个子查询的id分别是43238和43239，完全不重复），直接用UNION ALL即可。UNION ALL只会合并结果集，不会做去重操作，也就不需要对列做相等比较，从根源上避免问题。修改后的子查询部分如下：

select 43238::integer as id,
       NULL::json as forced_payload,
       NULL::character varying(255) as s3_original_path,
       NULL::character varying(255) as s3_image_path,
       NULL::character varying(255) as s3_preview_path,
       NULL::character varying(255) as s3_original_inbox_path,
       NULL::character varying(255) as s3_preview_inbox_path
union all
select 43239,
       NULL,
       NULL,
       NULL,
       NULL,
       NULL,
       NULL

方法二：将json类型转为jsonb类型 #

如果业务上必须使用UNION（比如可能存在重复行需要去重），可以把json类型替换为jsonb类型——jsonb是PostgreSQL中功能更完善的JSON类型，内置了相等比较运算符。

• 子查询中修改类型声明：NULL::jsonb as forced_payload
• 若pdfs表的forced_payload列是json类型，建议直接修改表结构为jsonb（长期来看更利于后续操作）：

  ALTER TABLE pdfs ALTER COLUMN forced_payload TYPE jsonb USING forced_payload::jsonb;
  

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