PGCon2011 - Final (2011.06.11)

PGCon 2011
The PostgreSQL Conference

Jeff Davis
Day Talks - 2 - 2011-05-20
Room DMS 1160
Start time 10:00
Duration 01:00
ID 311
Event type Lecture
Track Advanced Features
Language used for presentation English

Range Types

Range Types and Temporal Data

Range Types are a new feature that captures ranges -- ranges of time, ranges of numbers, ranges of dates -- and gives users the ability to create their own range types. Using Range Types improves performance by using range indexes, simplifies your queries and schema, and enables advanced features such as using Exclusion Constraints to prevent overlap.

Most organizations need to handle range types in one way or another. Ranges of numbers or IP addresses are useful; and of course, ranges of time are perhaps the most common. Explicitly telling PostgreSQL that you are working with ranges -- rather than simulating ranges with a "start" and "end" column -- offers major benefits for functionality (e.g. the ability to prevent overlap), performance (GiST indexing), and ease of use.

We'll walk through some basic examples, compare with the "two-column approach" (that is, using a "beginning" and an "end" column), show the benefits of range types using temporal data as an example, and give a brief overview of how to define your own range types.