How to check if a string is IN a text[]?

user_9570381 0
Viewed 38

This is the code I am running in postgres:

SELECT CASE WHEN 'oz' != ANY(('{oz,l,m,g}')::text[]) THEN 'HELOO' ELSE 'WOAH' END;

AFAIK, this should select 'WOAH' but it gives 'HELOO'. Now, with any string on the left side, it always selects 'HELOO' (I have tried 'monkey', and 'mg').

When I run the same code but now without a !:

SELECT CASE WHEN 'oz' = ANY(('{oz,l,m,g}')::text[]) THEN 'HELOO' ELSE 'WOAH' END;

It works as intended i.e. it selects 'HELOO' and when the left side string is 'monkey', it selects 'WOAH'.

How can I check here if the left side string is NOT in the right side text[]?

postgresql sql
3 Answers

From the documentation of ANY:

The left-hand expression is evaluated and compared to each element of the array using the given operator, which must yield a Boolean result. The result of ANY is "true" if any true result is obtained.

So when you say 'oz' != ANY(('{oz,l,m,g}')::text[]), Postgres examines in turn 'oz' != 'oz', 'oz' != 'l', 'oz' != 'm', and 'oz' != 'g'. Although the first of those is false, the others are true, so the ANY expression is true.

What you want instead is ALL, where:

The result is “false” if any false result is found.

So when you say 'oz' != ALL(('{oz,l,m,g}')::text[]), Postgres examines the same set of comparisons, but because the first one is false, the whole expression is false.

Effectively, ANY is using an OR between the possibilities, and ALL is using an AND.

This explains why NOT ('oz' = ANY(('{oz,l}')::text[])) also works: it is equivalent to NOT ('oz' = 'oz' OR 'oz' = 'l') and De Morgan's laws tell us that's the same as (NOT 'oz' = 'oz') AND (NOT 'oz' = 'l'); that is ('oz' != 'oz') AND ('oz' != 'l'), which is what we wanted.

user_1579570

You want <> ALL:

SELECT (CASE WHEN 'oz' <> ALL(('{oz,l,m,g}')::text[])
             THEN 'HELOO' ELSE 'WOAH'
        END);

Note that the important part is ALL; != also works but <> is the traditional SQL operator for not-equals.

I should note that you can also use:

SELECT (CASE WHEN NOT ('oz' = ANY(('{oz,l,m,g}')::text[]))
             THEN 'HELOO' ELSE 'WOAH'
        END);
user_11440350

demo:db<>fiddle

You can either use the construction: 'oz' = ANY('{...}') IS NOT TRUE

SELECT 
    CASE 
        WHEN 'oz' = ANY(('{oz,l,m,g}')::text[]) IS NOT TRUE THEN 'HELOO' 
        ELSE 'WOAH' 
    END;

or use a completely different quantifier function: ALL as @GordonLinoff said

user_39842210
Related