How many applications do you know that need to define a validity of a row? And how many of these do that by adding two columns: valid_from and valid_to or similar column names? Well, in PostgreSQL (you already suspect it 🙂 ) there is much more elegant way to do that. PostgreSQL knows the concept of range types. Several of these are pre-defined and you get them once you install PostgreSQL:

  • int4range — Range of integer
  • int8range — Range of bigint
  • numrange — Range of numeric
  • tsrange — Range of timestamp without time zone
  • tstzrange — Range of timestamp with time zone
  • daterange — Range of date

The classical example for using range types is a booking system for meeting rooms. Meeting rooms are booked for a period of time and the booking system has to know which periods are free before presenting possible choices. Lets do a simple test setup:

create extension if not exists btree_gist;
drop table if exists meeting_rooms_booked;
drop table if exists meeting_rooms;
create table meeting_rooms ( id int primary key
                           , mname varchar(20)
                           , location varchar(10)
                           );
create table meeting_rooms_booked ( mid int references meeting_rooms(id)
                                  , booking_range tsrange
                                  , exclude using gist (mid with =,booking_range with &&)
                                  );
insert into meeting_rooms ( id, mname, location)
       values ( 1, 'meetingsouth', 'south' )
            , ( 2, 'meetingnorth', 'north' )
            , ( 3, 'meetingwest', 'west' )
            , ( 4, 'meetingeast', 'east' );
insert into meeting_rooms_booked ( mid, booking_range )
       values ( 1, '[2015-01-01 15:00, 2015-01-01 18:30]' )
            , ( 1, '[2015-01-01 08:00, 2015-01-01 08:30]' )
            , ( 2, '[2015-03-01 17:00, 2015-03-01 18:30]' )
            , ( 1, '[2015-03-01 05:00, 2015-03-01 08:30]' )
            , ( 3, '[2015-02-01 15:00, 2015-02-01 18:30]' )
            , ( 4, '[2015-02-01 19:00, 2015-02-01 20:30]' )
            , ( 4, '[2015-03-01 15:00, 2015-03-01 18:30]' );

Lets say we want to know if meeting room number 3 is free at the 1st of February 2015 between 16:00 and 16:30. Using the range type operator “&&” (overlap) this is easy to ask:

postgres=# select * from meeting_rooms_booked where mid = 3;
 mid |                 booking_range                 
-----+-----------------------------------------------
   3 | ["2015-02-01 15:00:00","2015-02-01 18:30:00"]
(1 row)

postgres=# select booking_range && '[2015-02-01 16:00,2015-02-01 16:30)'::tsrange from meeting_rooms_booked where mid = 3;
 ?column? 
----------
 t
(1 row)

Indeed this does overlap. If we ask for a period for which the room is free we get false:

postgres=# select booking_range && '[2015-02-01 18:45,2015-02-01 19:15)'::tsrange from meeting_rooms_booked where mid = 3;
 ?column? 
----------
 f
(1 row)

If you go back to the table definition of meeting_rooms_booked the “&&” operator is used to enforce that there can not be two bookings which overlap in time. This is the reason the btree_gist extension was installed as normal btree indexes can not be created on range type columns.

So, if it is about a booking system or simple valid_from/valid_to definitions or something else which will benefit from this goody: range types are what you need 🙂


Thumbnail [60x60]
by
Daniel Westermann