Is it possible to combine the ANY and BETWEEN operator

Question

I need to check if any value in a Postgres array is between a given range. Something like this:

SELECT * FROM my_table WHERE ANY(my_array) BETWEEN 50 AND 60;

I realize that I can't actually do this, since the ANY operator must always be on the right side of the expression. Does anyone know of a convenient work around for doing something like this?

Answer 1

You can use range types, specifically the range membership operator @>:

SELECT * FROM my_table WHERE '[50,60]'::int4range @> ANY(my_array);

If you need to pull the range bounds from a column or parameter, the range constructor function might suit you better:

SELECT * FROM my_table WHERE int4range(a,b,'[]') @> ANY(my_array);