ANY and ALL in Postgres

The ANY and ALL operators allow us to query arrays like we query tuples and subqueries using the IN operator (The IN operator isn’t supported on arrays).

ANY does an OR of the provided operator

SELECT * FROM recipe
WHERE tag <> ANY(ARRAY['breakfast', 'snack', 'appetizer']);

-- equivalent query
SELECT * FROM recipe
WHERE tag <> 'breakfast' OR tag <> 'snack' OR tag <> 'appetizer';

ALL does an AND of the provided operator.

SELECT * FROM recipe
WHERE tag != ALL(ARRAY['breakfast', 'snack', 'appetizer']);

-- equivalent query
SELECT * FROM recipe
WHERE tag != 'breakfast' AND tag != 'snack' AND tag != 'appetizer';

My querying mistake

I had an array of strings (text[]) and I wanted to exclude any row that had a tag in the array.

My first thought was the following,

SELECT * FROM recipe
WHERE tag NOT IN ARRAY['breakfast', 'snack', 'appetizer'];

But we get a syntax error because IN doesn’t work for arrays!

ERROR:  syntax error at or near "ARRAY"
LINE 2: WHERE tag NOT IN ARRAY['breakfast', 'snack', 'appetizer'];

My next thought was using ANY, because we want the tag to not equal any of the array’s items, but this fails too because ANY is an OR of the operator, not AND.

SELECT * FROM recipe
WHERE tag <> ANY(ARRAY['breakfast', 'snack', 'appetizer']);

-- id   tag
-- 5   breakfast
-- 6   snack
-- 7   appetizer
-- 4   side
-- 3   entree
-- 2   dessert
-- 1   lunch

The following query using ALL correctly excludes any row where tag is in the array.

SELECT * FROM recipe
WHERE tag <> ALL(ARRAY['breakfast', 'snack', 'appetizer']);

-- id   tag
-- 4   side
-- 3   entree
-- 2   dessert
-- 1   lunch

Equivalent queries to filter based on arrays

For posterity, here are a few different ways to fetch the same information.

Find where tag is not in list

SELECT * FROM recipe
WHERE tag <> ALL(ARRAY['breakfast', 'snack', 'appetizer']);

SELECT * FROM recipe
WHERE NOT (tag = ANY(ARRAY['breakfast', 'snack', 'appetizer']));

SELECT * FROM recipe
WHERE tag NOT IN (
    SELECT unnest(ARRAY['breakfast', 'snack', 'appetizer'])
);

Find where tag is in list

SELECT * FROM recipe
WHERE tag = ANY(ARRAY['breakfast', 'snack', 'appetizer']);

SELECT * FROM recipe
WHERE NOT (tag = ALL(ARRAY['breakfast', 'snack', 'appetizer']));

SELECT * FROM recipe
WHERE tag IN (
    SELECT unnest(ARRAY['breakfast', 'snack', 'appetizer'])
);