Ranges in Postgres
We examine range types in PostgreSQL and how they can simplify queries and improve data integrity. This episode is brought to you by Hashrocket, expert consultants in PostgreSQL - learn more at https://hashrocket.com and www.pgcasts.com
Transcript:
(Note: this transcript has been condensed without code examples, as YouTube descriptions disallow angle brackets. Visit www.pgcasts.com for a full transcript including code snippets.)
A range type represents a range of values, for example 1 through 5 or January 10 through January 12. In a database without range types it is necessary to represent a range as two columns such as `start_date` and `end_date`. While this can work, it also can be difficult to work with. PostgreSQL range types simplify this dramatically. Let's look at some of the features we get with ranges.
First, let's use a range constructor function to build a range.
To find if a value is included in a range use the 'contains' inclusion operator.
We can also check if ranges overlap.
But what about the bounds of the range? Are they inclusive or exclusive?
The lower bound is inclusive.
The upper bound is exclusive.
This can be changed by passing a third argument to the range constructor. This third argument is a two-character string with the first character standing for the lower bound type and the second character standing for the upper bound type. Square brackets (`[` and `]`) mean inclusive and parenthesis (`(` and `)`) mean exclusive.
The default of inclusive lower and exclusive upper bounds is typically what you want.
This works nicely when testing for overlaps as it lets ranges just touch without overlap.
Ranges can also be made unbounded by using null as one of the edges.
Using a range instead of two separate columns also provides additional data integrity because it prevents ranges where the lower and upper boundaries are reversed.
In addition, PostgreSQL supports constraints that can prevent overlapping ranges from being stored. Suppose we are building a database to handle hotel reservations. A simple `reservations` table would have the room and date range.
Here's how to read the exclusion constraint. The first word `exclude` means exclude any row from matching any other row. `using gist` means that the underlying index type is GiST which stands for Generalized Search Tree. The expressions in the parenthesis are the match conditions. First check the room column with the equal operator (`=`). Then check for dates with the overlap operator (`&&`).
Whoops. PostgreSQL is giving us an error.
```
ERROR: data type character varying has no default operator class for access method "gist"
HINT: You must specify an operator class for the index or define a default operator class for the data type.
```
The problem is the GiST index does not define the `=` operator for `varchar`. We need to install the `btree_gist` extension.
This extension defines this and many other operators for GiST indexes.
Now let's try this out. First, we will insert a row for room 101.
We can insert a row with overlapping dates for a different room.
But we can't insert a row with overlapping dates for the same room.
As a bonus, not only does the exclusion constraint improve data integrity, the underlying index also can dramatically improve performance when searching with the overlap operator.
Thanks for watching.
Watch video Ranges in Postgres online without registration, duration 16 minute 40 second in high quality. This video was added by user PG Casts by Hashrocket 22 August 2016, don't forget to share it with your friends and acquaintances, it has been viewed on our site 3,034 once and liked it 59 people.