Filtering based on value presence with PostgreSQL's ALL and ANY operators

Filtering out rows with a given value present in an array in PostreSQL

When intuition works (static array inside the column of the table)

Let us say you have a row containing an array of enum values. Let us consider the enum:

CREATE TYPE my_enum AS ENUM ('foo', 'bar', 'baz');

This is contained in the table my_table:

create table my_table (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    values my_enum[] NOT NULL
);

I will prefill the data with some values:

INSERT INTO my_table (values) VALUES (ARRAY['foo', 'bar']);
INSERT INTO my_table (values) VALUES (ARRAY['bar']);
INSERT INTO my_table (values) VALUES (ARRAY['baz']);

Now let us assert we want to query for all the rows in which baz is not present.

I would intuitively write the query as:

SELECT * FROM my_table WHERE 'baz'::my_enum != ANY(values);

In plain English, this query reads as

”Please select all rows from my_table where baz is not equal to ANY of the items in the values column”.

You get the right result, containing the entries with foo and bar.

Postgres essentially loops over all the values in the column, and checks if the supplied value is equal to any of them

When intuition doesn’t work (dynamic array passed as an argument)

Let us consider this table:

create table my_table (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    value my_enum NOT NULL
);

I will prefill the data with some values:

INSERT INTO my_table (value) VALUES ('foo');
INSERT INTO my_table (value) VALUES ('bar');
INSERT INTO my_table (value) VALUES ('baz');

Now let us assert we want to query for all the rows in which value is not part of this array: foo, bar.

I would intuitively write the query as:

SELECT * FROM my_table WHERE value != ANY(ARRAY['foo', 'bar']); -- [foo, bar] would probably be dynamically passed as an argument

Just like before, in plain English, this query reads as:

“Please select all rows from my_table where value is not equal to ANY of the items in the array foo, bar”.

The result?

All the rows are returned, including foo and bar.

Why does this happen?

The intuitive “array in the column”:

Each value is compared to the specific array stored in the row. If the value differs from every element in that specific row’s array, the row is included.

The less intuitive “dynamic argument array comparison”:

Here, each row’s single value is compared to each item in the dynamic array. So, if the value differs from even one item in the array, the row is included. This is why e.g :

 'foo' != ANY(ARRAY['foo', 'bar'] 

was included in the result, as it differs from ‘bar’ in the array.

FIXED: The correct way to query when supplying a dynamic array as an argument

The correct way to write this query when supplying a comparison array as argument is actually to use the ALL operator:

SELECT * FROM my_table WHERE value != ALL(ARRAY['foo', 'bar']);