Why am I getting a syntax error when using ANY operator in PostgreSQL?

Question

I created a table in psql which is named restraunts to show the rest which all friends have gone to. Here is the table (friends and rest are the column names):

select distinct rest
from restraunts
where friends ilike any ('Micheal','Martin','Jack') and rest ilike any ('Ofelia','Casablanca');

I run the code but I got an error:

ERROR:  syntax error at or near ","
LINE 3: where friends ilike any('micheal','martin','jack') and rest ...
                                         ^

The error shows at the comma.

Why is this happening and how can I solve it?

Answer 1

The issue in your query is the usage of the ILIKE operator in combination with the ANY clause. You tried to use the ILIKE operator with a list of values directly, but this is not the correct syntax

Here is the correct way to do it:

SELECT DISTINCT rest
FROM restraunts
WHERE friends ILIKE ANY(ARRAY['Micheal', 'Martin', 'Jack']) AND rest ILIKE ANY(ARRAY['Ofelia', 'Casablanca']);