How to use a WHERE statement in Postgres with an array of OR combinations?

Question

I'm not sure how to phrase this question, but the premise is I have a table where the primary key is defined by two columns: row and col. I also want to query for many individual columns, which is where my problem comes into play.

If I had a simple column named id, I would be able to do a clause such as WHERE id=ANY($1) where $1 is an array of integers. However, with a primary key consisting of two columns, I wouldn't be able to the same.

WHERE row=ANY($1) AND col=ANY($2) gives me a region of what I want, but not the exact set of tuples that I need. Right now I'm generating a template query string with many conditions, such as:

WHERE row=$1 AND col=$2 OR
      row=$3 AND col=$4 OR ...

How can I avoid generating this "query template"? I don't think this is a very elegant solution, but it's the solution I have right now. Any insight would be appreciated!

Can't you do WHERE ARRAY[row,col] = ANY($1) where $1 is an array of arrays of integers? — melpomene
– melpomene, Commented Sep 23, 2017 at 23:46

Clodoaldo Neto · Accepted Answer · 2017-09-24 00:37:15Z

1

where (row,col) = any(array[(1,2),(3,4)])

or

where (row,col) in ((1,2),(3,4))

answered Sep 24, 2017 at 0:37

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

1 Comment

DonutGaz Over a year ago

Thanks, was having a rough time with the syntax. I've used Postgres quite a bit, but not enough to know the ins and outs.

Collectives™ on Stack Overflow

How to use a WHERE statement in Postgres with an array of OR combinations?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related