Get rows based on elements present inside the Postgres int array

Question

Below is my postgres 9.3 table

      id    arr
      1     [1,2,3]
      2     [1,2]
      3     [1]
      4     [2]
      5     [4,5,6]

If i search for 1,2 i am expecting output as below. I want all the rows which either contains 1 or 2.

  id    arr
  1     [1,2,3]
  2     [1,2]
  3     [1]
  4     [2]

I tried using below query i.e. using ANY

  Select * from test where arr = ANY ('{1,2}'::int[])

It gives an error

 ERROR: operator does not exist: integer[] = integer Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 30

How do I achieve this?

SQLFiddle - http://sqlfiddle.com/#!15/63c4f/4

Denys Séguret · Accepted Answer · 2016-06-10 11:29:54Z

1

Use the overlap operator:

Select * from test where arr && '{1,2}'::int[]

answered Jun 10, 2016 at 11:29

Denys Séguret

384k90 gold badges813 silver badges780 bronze badges

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

1 Comment

Max Maddy Over a year ago

You are the savior :)

Collectives™ on Stack Overflow

Get rows based on elements present inside the Postgres int array

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related