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?
CodePudding user response：
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:
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: