1

I have a table with an integer array column. I want to select all rows where contains at least one int value from a given list.

E.g.

SELECT * FROM table where (111) = ANY (columarray)

returns all rows where columnarray contains 111.

Now I want select all rows where columnarray contains 111 or/and 222. Something like

SELECT * FROM table where (111,222) = ANY (columnarray)

that doesnt work. It returns an error

Error: ERROR: operator does not exist: record = integer Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 45 SQLState: 42883 ErrorCode: 0

sylo
  • 207
  • 3
  • 16

1 Answers1

5

You can use the array overlap operator:

SELECT *
FROM table 
WHERE ARRAY[111, 222] && columnarray;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Do you know how i can parameterize the ARRAY values wit JPA? I try to use a NativeQuery. ARRAY:values and setParameter('values', valuesArray) doesnt work. – sylo Jan 29 '18 at 16:06