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(dot)klaver(at)aklaver(dot)com