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?

• Another table design?
• Put Integer.MIN and Integer.MAX for the null values?
• Maybe there is a query that can be used that can handle these null
  values?

CodePudding user response：

Use ranges:

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

A GiST index can make this query fast.

CodePudding user response：

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.