0

I want put a condition in my WHERE clause that returns true if a text field is contained in a subset of an array declared in the statement. This is using postgres v15.1. I have simplified the statement to the following example:

SELECT 'case_1'::TEXT IN((ARRAY['case_1','case_2','case_3']) [1:2]);

This should declare an array containing the case_1,2,3, take a subset of the array containing case_1,2 and then test if case_1 is in the array. Expected result is true.

Actual behaviour is an error: "Operator does not exist text=text[]".

I understood the IN() operator is a test if the left hand argument is in the right hand argument which has a dimension.

How can I correct the syntax to return true or false if case_1 is in the subset of the array?

acritely
  • 77
  • 7

1 Answers1

2

Like the error says, you've got your types messed up.

The IN keyword expects an explicit list of items in brackets. You have provided it with one item - an array.

You want ANY(...)

SELECT 'case_1' = ANY((ARRAY['case_1', 'case_2', 'case_3'])[1:2]);
Richard Huxton
  • 21,516
  • 3
  • 39
  • 51