Difference between 'NOT IN' and '!= ANY'-CodePudding

PostgreSQL version: 14

I have a query that updates a jsonb field and removes items with certain IDs from it:

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)

Value before query:

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

Input:

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

But the value does not change after the query.

But if I replace != ANY() with NOT IN, it works. Why does this happen?

Here is db-fiddle with example: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=9c3478143eec72175dfa2e9768254f68

CodePudding user response：

The equivalence of NOT IN is != ALL, not != ANY. With != ANY, you are checking if any value (i.e. at least one) is not equal, which is not what you want.

CodePudding user response：

Before we jump to your question let's understand the difference between != and NOT

!= is a binary operator that returns true if its two arguments are not equal.

NOT is a unary operator, which reverses its argument, a Boolean expression.

So to your problem statement, != ANY is actually evaluating the left & right sides if any value is not equal. For your case you probabilly looking for != ALL.