PostgreSQL wildcard LIKE for any of a list of words

PostgreSQL also supports full POSIX regular expressions:

select * from table where value ~* 'foo|bar|baz';

The ~* is for a case insensitive match, ~ is case sensitive.

Another option is to use ANY:

select * from table where value  like any (array['%foo%', '%bar%', '%baz%']);
select * from table where value ilike any (array['%foo%', '%bar%', '%baz%']);

You can use ANY with any operator that yields a boolean. I suspect that the regex options would be quicker but ANY is a useful tool to have in your toolbox.

Actually there is an operator for that in PostgreSQL:

SELECT *
FROM table
WHERE lower(value) ~~ ANY('{%foo%,%bar%,%baz%}');

You can use Postgres' SIMILAR TO operator which supports alternations, i.e.

select * from table where lower(value) similar to '%(foo|bar|baz)%';