Re: Overlapping timestamptz ranges with priority

On 6/28/21 3:05 AM, Ray O'Donnell wrote:
> On 28/06/2021 00:52, Adrian Klaver wrote:
>> On 6/27/21 3:41 PM, Ray O'Donnell wrote:
> 
>>> Here's a slightly simplified example:
>>>
>>>
>>> create table bookings (
>>>      booking_id bigint not null,
>>>      booking_time tstzrange not null,
>>>
>>>      constraint bookings_pk primary key (booking_id)
>>> );
>>
>> It seems to me this is missing some reference to what is being booked 
>> e.g. room number.
> 
> Yes, indeed - I left out everything except what was immediately relevant 
> to my problem. The real table is actually for booking aircraft - it's 
> for the local flying club of which I'm a member - so there are columns 
> for aircraft registration, member details, etc.

An ounce of prevention is worth a pound of cure:

1) Install btree_gist
create extension btree_gist ;

2) create table bookings (
     booking_id bigint not null,
     aircraft_id integer,
     booking_time_start timestamptz,
     booking_time_end timestamptz,

     constraint bookings_pk primary key (booking_id),
     constraint timestamp_exclude EXCLUDE USING gist
         (aircraft_id WITH =,
          tstzrange(booking_time_start, booking_time_end, '[]') WITH &&)
);

3)
insert into bookings (booking_id, aircraft_id,  booking_time_start, 
booking_time_end) values
(1, 1, '2021-06-20 12:00+01', '2021-06-20 14:00+01');
INSERT 0 1

insert into bookings (booking_id, aircraft_id,  booking_time_start, 
booking_time_end) values
(2, 1, '2021-06-20 13:00+01', '2021-06-20 16:00+01');
ERROR:  conflicting key value violates exclusion constraint 
"timestamp_exclude"
DETAIL:  Key (aircraft_id, tstzrange(booking_time_start, 
booking_time_end, '[]'::text))=(1, ["2021-06-20 05:00:00-07","2021-06-20 
08:00:00-07"]) conflicts with existing key (aircraft_id, 
tstzrange(booking_time_start, booking_time_end, '[]'::text))=(1, 
["2021-06-20 04:00:00-07","2021-06-20 06:00:00-07"]).


This way the overlap is prevented and you don't have to deal with it later.

> 
> Ray.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com