SQL between with nullable min and max value

Question

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

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?

Please add the expected result to your question. It seems that you would need to enhance your WHERE condition with IS NULL OR boolean constructs. — PM 77-1
– PM 77-1, Commented Jul 12, 2022 at 15:44
You could give your min and max an upper and lower bound like WHERE COALESCE(min, -9999999) <= some_value AND some_value <= COALESCE(max, 9999999) (as an option) — JNevill
– JNevill, Commented Jul 12, 2022 at 15:44
That was something in the lines of what I thought, but by just replacing the null upper and lower bound in the records themselves instead of using COALESCE function. — Tom
– Tom, Commented Jul 12, 2022 at 15:48

Laurenz Albe · Accepted Answer · 2022-07-12 17:59:29Z

3

Use ranges:

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

A GiST index can make this query fast.

answered Jul 12, 2022 at 15:51

Laurenz Albe

256k22 gold badges310 silver badges386 bronze badges

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

Comments

Mike Organek · Accepted Answer · 2022-07-12 18:59:08Z

3

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.

answered Jul 12, 2022 at 15:51

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

You mean '[]' as third argument, right?

@LaurenzAlbe You are right. Sorry for my mistake.