Search Postgresql Archives

Re: Overlapping timestamptz ranges with priority

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

 



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.klaver@xxxxxxxxxxx





[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