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.

回答1:

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

• start and end day are the same
• start / end can must be on 30 min boundary
• and between 8:00 .. 18:00
• only weekdays

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

回答2:

You can solve everything except item 3. with plain CHECK constraints:

Use dedicated range functions lower(anysrange) and upper(anyrange) to access lower / upper border of the range.

1.) Start and end dates in tsrange are always same.

CONSTRAINT schedule_same_day
CHECK (lower(during)::date = upper(during)::date)

2.) Start and end dates cannot be saturday and sunday

Use isodow, not dow for a simpler expression.

CONSTRAINT schedule_no weekend
CHECK (EXTRACT(ISODOW FROM lower(during)) < 6)  -- upper on same day

3.) Start and end dates cannot appear in public holiday table

The only exception: for this you'd need a trigger like:

CREATE OR REPLACE FUNCTION trg_during_no_holy()
  RETURNS trigger AS
$func$
BEGIN

IF EXISTS (SELECT 1 FROM holiday WHERE day = lower(NEW.during)) THEN
    RAISE EXCEPTION 'Day too holy: %', lower(NEW.during);
END IF;

RETURN NEW;

END
$func$ LANGUAGE plpgsql VOLATILE;

CREATE TRIGGER insupbef_holycheck
  BEFORE INSERT OR UPDATE
  ON schedule
  FOR EACH ROW
  EXECUTE PROCEDURE trg_during_no_holy();

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

CONSTRAINT schedule_8_inc_to_18_exc_half_hours
 CHECK (lower(during)::time BETWEEN time '8:00'AND time '17:30' -- time range
    AND upper(during)::time BETWEEN time '8:30'AND time '18:00'
    AND EXTRACT(MINUTE FROM lower(during)) IN (0, 30) -- only :00 or :30
    AND EXTRACT(MINUTE FROM upper(during)) IN (0, 30)
    AND lower_inc(during)          -- lower bound always incl.
    AND upper_inc(during) = FALSE  -- upper bound always excl.
)

Additional question in comment

How to restrict seconds and fractional seconds to 0 only?

Simplest way: Cast to timestamp(0) or timestamptz(0) or use this data type for the column to begin with. I quote the manual here:

time, timestamp, and interval accept an optional precision value p which specifies the number of fractional digits retained in the seconds field.