Search Postgresql Archives

Re: Overlapping timestamptz ranges with priority

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxx





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux