Re: Overlapping timestamptz ranges with priority

On 6/29/21 12:49 PM, Ray O'Donnell wrote:
> On 29/06/2021 20:43, Adrian Klaver wrote:
>>
>> 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 &&)
> 
> [...]
> 
>> This way the overlap is prevented and you don't have to deal with it 
>> later.
> 
> Fair point.... The idea of using overlapping ranges was to allow for 
> queued bookings, which is something we permit. In the old system (which 
> this one is to replace) queued bookings are kept in a separate table. My 
> idea was to have them in a single table, which would seem more elegant - 
> but by golly it's harder! Maybe I should rethink my approach.

The queued bookings are for a particular aircraft or a particular time slot?

> 
> Thanks,
> 
> Ray.
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com