Check multiple ids exists in Postgres int[]

Question

I am trying to get rows where a column of type int[] contains a value. currently i am using Any() operator but not working for me.

select * from products where 4 = ANY(product_type) AND (3,5) @> ANY(category_id);

given error:

Syntax error: 7 ERROR: syntax error at or near "," LINE 1: ...mages.product_id where 4 = ANY(product_type) AND 3,5 @> ANY(category_id)

@Usagi Miyamoto: sorry but '(3,5)'::int[] not working for me — vicky793
– vicky793, Commented Aug 14, 2020 at 6:51

score 1 · Accepted Answer · 2020-08-14 06:54:17Z

1

You need to use the contains operator in a different way:

The following will test if category_id contains both the values 3 and 5

AND category_id @> array[3,5]

If you want to check if it contains at least one of them, use the overlaps operator:

AND category_id && array[3,5]

answered Aug 14, 2020 at 6:36

user330315

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

4 Comments

given error operator does not exist: integer[] = integer

@vicky793: I didn't realize category_id is actually an array. See my edit.

category_id @> array[3,5] will most certainly return those rows: dbfiddle.uk/…

That is not the code from my answer. I wrote category_id @> array[3,5] you wrote category_id @> 3,5