How to check if a float is between multiple ranges in Postgres?

Question

I'm trying to write a query like this:

SELECT * FROM table t
WHERE ((long_expression BETWEEN -5 AND -2) OR 
       (long_expression BETWEEN 0 AND 2) OR 
       (long_expression BETWEEN 4 and 6))

Where long_expression is approximately equal to this:

(((t.s <#> (SELECT s FROM user WHERE user.user_id = $1)) / (SELECT COUNT(DISTINCT cluster_id) FROM cluster) * -1) + 1)

t.s and s are the CUBE datatypes and <#> is the indexed distance operator.

I could just repeat this long expression multiple times in the body, but this would be extremely verbose. An alternative might be to save it in a variable somehow (with a CTE?), but I think this might remove the possibility of using an index in the WHERE clause?

I also found int4range and numrange, but I don't believe they would work here either, because the distance operator returns float8's, not integer or numerics.

You could create a temporary table containing the long expression, and the index it. — Tim Biegeleisen
– Tim Biegeleisen, Commented Nov 8, 2019 at 3:08

Gordon Linoff · Accepted Answer · 2019-11-08 11:42:05Z

1

You can use a lateral join:

SELECT t.*
FROM table t CROSS JOIN LATERAL
     (VALUES (long_expression)) v(x)
WHERE ((v.x BETWEEN -5 AND -2) OR 
       (v.x BETWEEN 0 AND 2) OR 
       (v.x BETWEEN 4 and 6)
      );

Of course, a CTE or subquery could be used as well; I like lateral joins because they are easy to express multiple expressions that depend on previous values.

edited Nov 8, 2019 at 11:42

answered Nov 8, 2019 at 3:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

How to check if a float is between multiple ranges in Postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related