1

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

enter image description here

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?

2
  • ...ANY (ARRAY['ios', 'windows']) Commented Jun 7, 2022 at 19:39
  • 1
    SELECT * FROM Projects WHERE operating_system && ARRAY['ios', 'windows'] Commented Jun 7, 2022 at 20:03

1 Answer 1

2

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:

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.