CODEWITHSUNDEEP
sqlpostgresqlindexingbetween
Chron Bag
Chron Bag

Reputation: 587

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

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.

Upvotes: 0

Views: 1309

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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.

Upvotes: 1

Related Questions