Using ANY to check if any value of an array is contained in an array of enums

Question

I have a table projects with a column operating_sytem which is an enum array like shown below:

Furthermore, I have an array like ['ios', 'windows'] and I want to select all the projects that have in operating_system column any value of the array So it should return 1st, 4th, 7th and 9th occurrence

I tried:

SELECT * FROM public.projects
WHERE operating_system = ANY (['ios', 'windows']);

but it returned an error:

ERROR:  syntax error at or near "["  
LINE 2: WHERE operating_system = ANY (['ios', 'windows']);

How can I do this query?

Answer 1

You are confusing the syntax of array constructor and array literal (array constant):

What's more, an array constructor would require an explicit cast, else text is assumed, and there is almost certainly no operator for operation_system_enum = text in your system.

So these would work with array constructor if operating_system was indeed a single operating system like the name implies:

... WHERE operating_system = ANY (ARRAY['ios'::operation_system_enum, 'linux']);

... WHERE operating_system = ANY (ARRAY['ios', 'linux']::operation_system_enum[]);

Or simpler with an (optionally untyped) array literal:

... WHERE operating_system = ANY ('{ios, linux}');

Here, the eventual type can be derived from the expression. See:

• How to pass custom type array to Postgres function
• How to use ANY instead of IN in a WHERE clause?

Actual answer

But since operating_system is actually type operation_system_enum[], so an array of operating systems, you really need the array "overlaps" operator &&, like Oto suggested:

... WHERE operating_system && ARRAY['ios'::operation_system_enum, 'linux'];

Or simpler with an (untyped) array literal:

... WHERE operating_system && '{ios, linux}';

The last one is the way to go.
Only expressions with array operators can tap into a GIN index to begin with. See:

• Can PostgreSQL index array columns?