Search code examples
arrayspostgresqlanypostgres-14

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

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

Solution

  • 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.