Re: Overlapping timestamptz ranges with priority

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.

Thanks,

Ray.

--
Raymond O'Donnell // Galway // Ireland
ray(at)rodonnell(dot)ie