5

I'm trying to select rows where a value is present in any of a collection of ranges. So far, I've only been able to make the following work:

SELECT * FROM table
WHERE num <@ numrange(1,4) OR num <@ numrange(7,11)

I was hoping I would be able to get something like

SELECT * FROM table
WHERE num <@ ANY(numrange(1,4), numrange(7,11))

working, but I haven't had any luck with it.

Any idea of a better solution than chaining ORs together?

pgoggijr
  • 414
  • 1
  • 3
  • 15

1 Answers1

3

You were close... The value in the ANY(...) expression needs to be an array:

SELECT * FROM table
WHERE num <@ ANY(ARRAY[numrange(1,4), numrange(7,11)])
Nick Barnes
  • 19,816
  • 3
  • 51
  • 63
  • would you happen to have any hints on indexing on the num column as well? – pgoggijr Apr 29 '16 at 14:47
  • The `<@` operator is going to make that difficult. In that case, I'd probably just write the query as `num BETWEEN 1 AND 3 OR ...`, which should have no problems using the index. – Nick Barnes Apr 29 '16 at 16:04