Understanding ILIKE ANY element of an array - postgresql

Question

I have to select all the lines in a table (let's call it mytable) for which the value in a given column (let's call it mycolumn) is not equal to 'A' and not equal to 'S'.

So I tried something like

SELECT * FROM mytable WHERE mycolumn NOT ILIKE ANY(ARRAY['A','S'])

I prefer the use of ILIKE instead of the use of = to test string equalities because the values 'A' and 'S' may come in lower-case in my data, so I want the values 's' and 'a' to be excluded as well.

Strangely enough, the query above did return some lines for which the value inside mycolumn was equal to 'A'. I was very surprised.

Therefore, to understand what was happening I tried to carry out a very simple logical test:

SELECT ('A' ILIKE ANY(ARRAY['A','S'])) as logical_test ;

The statement above returns TRUE, which was expected.

But the following statement also returns TRUE and this is where I'm lost:

SELECT ('A' NOT ILIKE ANY(ARRAY['A','S'])) as logical_test ;

Could someone explain why 'A' NOT ILIKE ANY(ARRAY['A','S']) is considered TRUE by PostgreSQL?

Answer 1

The result of the ANY comparison is true if at least one element from the array qualifies for the condition.

As 'S' is different from 'A' the result of ANY is true (because at least one element was different).

You are looking for the ALL operator:

SELECT * 
FROM mytable 
WHERE mycolumn NOT ILIKE ALL(ARRAY['A','S'])