PostgreSQL 14中NOT IN与!= ANY的差异及jsonb更新失效问题

问题场景 #

我在PostgreSQL 14中编写了一条更新JSONB字段、移除指定ID元素的SQL语句：

UPDATE types
SET elements = (
    SELECT
        CASE
            WHEN jsonb_agg(element_obj) IS NULL THEN
                '[]'::JSONB
            ELSE
                jsonb_agg(element_obj)
        END
     FROM jsonb_array_elements(elements) AS t(element_obj)
     WHERE element_obj ->> 'id' != ANY(element_ids::TEXT[])
)
WHERE id = ANY(type_ids)

执行前elements字段值：

[
   {"id": "260c7f69-bc8c-49c2-b65b-cb895da3aa2a", "type": 1},
   {"id": "7e3211cb-8919-4941-b3d1-a7524493b03a", "type": 12}, 
   {"id": "c4816652-ec62-4f83-aebd-1ec468d1d4a3", "type": 6}
]

输入参数：

element_ids := ARRAY [
    '260c7f69-bc8c-49c2-b65b-cb895da3aa2a',
    '7e3211cb-8919-4941-b3d1-a7524493b03a'
]::TEXT[]

执行后字段值未发生变化，但将!= ANY替换为NOT IN后语句正常生效。

核心原因：!= ANY与NOT IN的逻辑差异 #

PostgreSQL中，这两个运算符的语义完全不同：

• != ANY(array)：只要数组中存在至少一个元素与当前值不相等，条件就返回true。
  比如对于要移除的ID260c7f69-bc8c-49c2-b65b-cb895da3aa2a，数组中存在另一个ID7e3211cb-8919-4941-b3d1-a7524493b03a，满足当前ID != 这个元素，因此!= ANY条件为true，该元素不会被过滤，最终被保留在聚合结果中。
• NOT IN(array)：只有当前值不在数组的所有元素中，条件才返回true。
  对于要移除的ID，它存在于数组中，因此NOT IN条件为false，元素会被过滤，符合预期。

等价替代写法 #

除了NOT IN，还可以使用<> ALL(array)，它的语义和NOT IN完全一致，也能达到预期效果：

WHERE element_obj ->> 'id' <> ALL(element_ids::TEXT[])

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