Postgresql Range Basics

Tue, 18 Mar 2014 00:00:00 +0000

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.

Postgresql on David Hamp-Gonsalves

Postgresql Range Basics

Range Types

Basic Operations

Defining Ranges Values