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