Select all rows where array contains values from a given list in Postgres

Question

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

Gordon Linoff · Accepted Answer · 2018-01-29 11:04:55Z

5

You can use the array overlap operator:

SELECT *
FROM table 
WHERE ARRAY[111, 222] && columnarray;

answered Jan 29, 2018 at 11:04

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

1 Comment

sylo Over a year ago

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.

Collectives™ on Stack Overflow

Select all rows where array contains values from a given list in Postgres

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related