Database Reference
In-Depth Information
The other noteworthy aspect of check constraints is that unlike primary key, foreign
key, and unique key constraints, they inherit from parent tables.
Exclusion Constraints
Introduced in version 9.0, exclusion constraints allow you to incorporate additional
operators to enforce uniqueness that can't be satisfied by the equality operator. Exclusion
constraints are especially useful in problems involving scheduling.
PostgreSQL 9.2 introduced the range data types that are perfect candidates for exclusion
constraints. You'll find a fine example of using exclusion constraints for range data types
Exclusion constraints are generally enforced using GiST indexes, but you can create
compound indexes that incorporate B-Tree as well. Before you do this, you need to
install the
btree_gist
extension. A classic use of a compound exclusion constraint is
for scheduling resources.
Here's an example using exclusion constraints. Suppose you have a fixed number of
conference rooms in your office, and groups must book them in advance. See how we'd
prevent double-booking in
Example 6-6
. Take note of how we are able to use the overlap
operator (
&&
) for our temporal comparison and the usual equality operator for the room
number.
Example 6-6. Prevent overlapping bookings for same room
CREATE
TABLE
schedules
(
id
serial
primary
key
,
room
smallint
,
time_slot
tstzrange
);
ALTER
TABLE
schedules
ADD
CONSTRAINT
ex_schedules
EXCLUDE
USING
gist
(
room
WITH
=
,
time_slot
WITH
&&
);
Just as with uniqueness constraints, PostgreSQL automatically creates a corresponding
index of the type specified in the constraint declaration.
Indexes
PostgreSQL ships stocked with a lavish framework for creating and fine-tuning indexes.
The art of PostgreSQL indexing could fill a tome all by itself. At the time of writing,
PostgreSQL comes with at least four types of indexes, often referred to as
index meth‐
ods
. If you find these insufficient, you can define new index operators and modifiers to
supplement them. If still unsatisfied, you're free to invent your own index type.
PostgreSQL also allows you to mix and match different index types in the same table
with the expectation that the planner will consider them all. For instance, one column
could use a B-Tree index while an adjacent column uses a GiST index, with both indexes
contributing to the speed of the query. To delve more into the mechanics of how the
planner takes advantage of indexes, visit
bitmap index scan strategy
.
