In postgres 9.2 a new family of data types were added called Ranges. This is a quick overview of how to use them.
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.
- 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 _lowerinf and _upperinf. 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.
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 &&) );