sql - 'Not ilike any' clause with wildcard from an array doesn't work

Question

Just as in title, following code doesn't filter anything at all, it returns all rows while without 'NOT' it works just fine. Where's the problem?

create table test (value text);

    insert into test values
    ('Test1'),
    ('foo'),
    ('bar'),
    ('foobar'),
    ('foobar2'),
    ('foobar3'),
    ('foo1bar'),
    ('foo bar'),
    ('barbar');
    
    select *
    FROM test
    WHERE value NOT like any (array['%foo%', '%foo bar%', '%xx%']) 

https://www.db-fiddle.com/f/gCFy3K97gQy7gxomAL2Qm7/0

question from:https://stackoverflow.com/questions/65891325/not-ilike-any-clause-with-wildcard-from-an-array-doesnt-work

Answer 1

I assume you mean not like any is not working. And I think that is because you want not like all:

SELECT *
FROM test
WHERE value  like all (array['%foo%', '%foo bar%', '%xx%']) 

Here is a db<>fiddle.