sqlpostgresqldatabase-designschedulepostgresql-9.2

Allow only work time in reservations table

PostgreSql 9.2 Reservation table is defined as

CREATE EXTENSION btree_gist;
CREATE TABLE schedule (
  id serial primary key,
  during tsrange not null,
  EXCLUDE USING gist (during WITH &&)
);

Holidays are listed in table 

CREATE TABLE holiday ( day primary key );

Work hours are from 8 to 18:00 in work days and reservatons can be done by 30 minute intervals only. How to add constraints to during values so that it allows only reservations during work time:

  1. Start and end dates in tsrange are always same.
  2. Start and end dates cannot be saturday and sunday
  3. Start and end dates cannot appear in public holiday table
  4. Start time can be only 8:00 , 8:30, 9:00, 9:30, ... 16:00, 16:30, 17:00 or 17:30 inclusive
  5. End time can be only 8:30, 9:00, 9:30, ... 16:00, 16:30, 17:00, 17:30 or 18:00 exclusive

Is it reasonable to add those or some of those constraints to this table ? If yes, how to add ? schedule table structure can changed if this helps.

Solution

  • You need to change you table definition and add some check constraints:

    CREATE TABLE schedule (
  id serial primary key,
  during tsrange not null check(
    (lower(during)::date = upper(during)::date) and 
    (date_trunc('hour', upper(during)) + INTERVAL '30 min' * ROUND(date_part('minute', upper(during)) / 30.0) = upper(during)) and
    (date_trunc('hour', lower(during)) + INTERVAL '30 min' * ROUND(date_part('minute', lower(during)) / 30.0) = lower(during)) and
    (lower(during)::time >= '8:00'::time and upper(during)::time <= '18:00'::time) and
    (date_part('dow', lower(during)) in (1,2,3,4,5) and date_part('dow', upper(during)) in (1,2,3,4,5))
  ),
  EXCLUDE USING gist (during WITH &&)
);

    The checks are in this order

    We need something in holiday table: insert into holiday values ('2012-11-28');

    check can not reference other table hence we need trigger function (it might be better to put all check into this function i.e. have them at one place):

    create function holiday_check() returns trigger language plpgsql stable as $$
begin
    if exists (select * from holiday where day in (lower(NEW.during)::date, upper(NEW.during)::date)) then
        raise exception 'public holiday';
    else
        return NEW;
    end if;
end;
$$;

    Then we need to create triggers before insert/update:

    create trigger holiday_check_i before insert on schedule for each row execute procedure holiday_check();
create trigger holiday_check_u before update on schedule for each row execute procedure holiday_check();

    Finally, some tests:

    -- OK
insert into schedule(during) values (tsrange('2012-11-26 08:00', '2012-11-26 09:00'));
INSERT 0 1

-- out of business hours
insert into schedule(during) values (tsrange('2012-11-26 04:00', '2012-11-26 05:00'));
ERROR:  new row for relation "schedule" violates check constraint "schedule_during_check"
DETAIL:  Failing row contains (12, ["2012-11-26 04:00:00","2012-11-26 05:00:00")).

-- End time can be only 8:30, 9:00, 9:30, ... 16:00, 16:30, 17:00, 17:30 or 18:00 exclusive
insert into schedule(during) values (tsrange('2012-11-26 08:00', '2012-11-26 09:10'));
ERROR:  new row for relation "schedule" violates check constraint "schedule_during_check"
DETAIL:  Failing row contains (13, ["2012-11-26 08:00:00","2012-11-26 09:10:00")).

-- Start time can be only 8:00 , 8:30, 9:00, 9:30, ... 16:00, 16:30, 17:00 or 17:30 inclusive
insert into schedule(during) values (tsrange('2012-11-26 11:24', '2012-11-26 13:00'));
ERROR:  new row for relation "schedule" violates check constraint "schedule_during_check"
DETAIL:  Failing row contains (14, ["2012-11-26 11:24:00","2012-11-26 13:00:00")).

-- holiday
insert into schedule(during) values (tsrange('2012-11-28 10:00', '2012-11-28 13:00'));
ERROR:  public holiday