A couple of years ago PostgreSQL introduced so called “exclusion operators”. This feature is not very well known. However, people can use it to implement highly sophisticated constraints, which are very hard to do on the application level. In a way “exclusion operators” are “beyond unique” …
Before looking at some sophisticated stuff, let us take a look at a simple example: In many cases people want to avoid that bookings can overlap each other. Somebody might want to make sure that the very same car is not recent out to various people at the same time or you might just want to make sure that a driver is not supposed to drive two cars at the same time.
PostgreSQL offers a nice way to achieve that: When creating a table you can simple add “EXCLUDE USING gist” along with a nice restriction:
CREATE TABLE car_reservation ( car text, during tsrange, EXCLUDE USING GIST (car WITH =, during WITH &&) );
In this case PostgreSQL will build an index covering both columns and it will ensure that the timerange column is not allowed to contain overlapping data (for the very same car or course).
A more practical example
However, in real life things might be a bit more complicated. Let us assume we are dealing with rental cars. If a customer returns a car, it might actually need some cleaning and the company might decide to not instantly rent it out again until it is properly prepared for the next client.
The question now is, how can we tell PostgreSQL about this business requirement?
The solution to the problem is actually quite simple. It is possible to use a function call in the EXCLUDE clause to calculate the real range we want to exclude. Here is an example:
CREATE FUNCTION add_buffer(tsrange, interval) RETURNS tsrange AS $$ SELECT tsrange(lower($1) - $2, upper($1) + $2); $$ LANGUAGE 'sql' IMMUTABLE;
The important thing is that the function is IMMUTABLE. We need a perfectly deterministic return value here because otherwise the process does not work at all.
In our case we simply add a safety buffer before and after the interval added by the end user. In the next step we can install the btree_gist extension to handle standard data types with gist indexes:
CREATE EXTENSION IF NOT EXISTS btree_gist;
Now we can use the function in the table definition:
CREATE TABLE car_reservation ( car text, during tsrange, EXCLUDE USING GIST (car WITH =, add_buffer(during, '1 hours'::interval) WITH &&) );
In this case a 1 hour safety buffer should be added before and after the interval added by the end user.
Now we can test the setup:
INSERT INTO car_reservation VALUES ('auto 1', '["2011-01-01 00:00", "2011-01-01 01:00"]'); INSERT INTO car_reservation VALUES ('auto 1', '["2010-01-01 00:00", "2010-01-01 01:00"]'); INSERT INTO car_reservation VALUES ('auto 1', '["2010-01-01 01:30", "2010-01-01 02:30"]');
The test data should error out, which is exactly what is going to happen:
psql test < script.sql BEGIN CREATE FUNCTION NOTICE: extension "btree_gist" already exists, skipping CREATE EXTENSION CREATE TABLE INSERT 0 1 INSERT 0 1 ERROR: conflicting key value violates exclusion constraint "car_reservation_car_add_buffer_excl" DETAIL: Key (car, add_buffer(during, '01:00:00'::interval))=(auto 1, ["2010-01-01 00:30:00","2010-01-01 03:30:00")) conflicts with existing key (car, add_buffer(during, '01:00:00'::interval))=(auto 1, ["2009-12-31 23:00:00","2010-01-01 02:00:00")).