In postgres 9.2 a new family of data types were added called Ranges. This is a quick overview of how to use them.

Range Types

There are a handful of built in range types that cover the expected number and date types and you can also define your own if you need something outside of that.

Basic Operations

• Defining Range - CREATE TABLE ranges (range int4range)
• Inserting Range - INSERT INTO ranges VALUES(’[1, 4]')
• Infinate Ranges - INSERT INTO ranges VALUES(’[4, infinity]')
• Check Range Membership - SELECT * FROM ranges where range @> 2
• Get Range Bounds - SELECT lower(range), upper(range) FROM ranges

Defining Ranges Values

You can define inclusive ranges using [2,3] and exclusive ranges using (1,4). You can also mix them [2,4). You can create infinate ranges like [4, infinaty] or [-infinity, 4] and you can test for infinity using lower_inf and upper_inf. Keep in mind that creating a range like [NULL, 4] will create a unbound lower range. This will act like [infinity, 4] but you wont be able to test for infinity since NULL is still distinct from infinity.

Non-Overlapping Ranges

I find that when using ranges you often need to create non-overlapping ranges. You can add such a condition using like:

CREATE TABLE ranges (
  range int4range,
  EXCLUDE USING gist (range WITH &&)
);