Postgresql Range Basics

March 2014 ยท 2 minute read

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 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.

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 &&)
);