Querying array using list in postgreSQL

Question

I have a table in postgre SQL like this which using 1 dimentional integer data type in array column

+----+--------+-----------+
| id |  name  |   array   |
+----+--------+-----------+
|  1 | apple  | {1, 2, 3} |
|  2 | mango  | {2, 3, 4} |
|  3 | banana | {4, 5, 6} |
+----+--------+-----------+

and I want to do a query to find every rows with array column contains at least one number from my list of number. For example, search each array to see if it contains 2, 4

Is there any better solution than using query like this?

SELECT * FROM table WHERE 2 = ANY (array) OR 4 = ANY (array)

Tim Biegeleisen · Accepted Answer · 2016-12-03 06:53:11Z

2

You can try the following:

SELECT *
FROM yourTable
WHERE '{1,4}' && array

answered Dec 3, 2016 at 6:53

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

Collectives™ on Stack Overflow

Querying array using list in postgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related