CODEWITHSUNDEEP
sqlpostgresql
Tom
Tom

Reputation: 252

SQL between with nullable min and max value

Let's say I have a table with following columns.

id min max
1 null -101
2 -100 100
3 101 200
...
99 1000 null

I want to be able to find the record based on a value parameter where min <= value and value >= max.

SELECT *
FROM my_table t
WHERE min <= some_value and some_value <= max

The problem is that the lowest and the highest record have no upper or lower bound.

My question is what is the best practice for these cases?

Upvotes: 1

Views: 368

Answers (2)

Mike Organek
Mike Organek

Reputation: 12484

You can use the range types and range functions built into PostgreSQL to handle this:

SELECT *
  FROM my_table t
 WHERE int8range(min, max, '[]') @> somevalue;

The '[]' argument makes the min and max values inclusive, and a null for either min or max makes the range unbounded on that side.

Upvotes: 3

Laurenz Albe
Laurenz Albe

Reputation: 246698

Use ranges:

WHERE int4range(min, max, '[]') @> some_value

A GiST index can make this query fast.

Upvotes: 3

Related Questions