您需要更改表定义并添加一些
check约束：
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 &&));支票按此顺序
- 开始日期和结束日期相同
- 开始/结束必须在30分钟的边界上
- 至8:00 .. 18:00之间
- 只有工作日
我们需要在
holiday表中添加一些内容：将其插入假日值（‘2012-11-28’）;
check不能引用其他表，因此我们需要触发函数（最好将所有检查都放到该函数中，例如将它们放在一个位置）：
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;$$;
然后我们需要在
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();
最后，进行一些测试：
-- OKinsert into schedule(during) values (tsrange('2012-11-26 08:00', '2012-11-26 09:00'));INSERT 0 1-- out of business hoursinsert 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 exclusiveinsert 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 inclusiveinsert 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")).-- holidayinsert into schedule(during) values (tsrange('2012-11-28 10:00', '2012-11-28 13:00'));ERROR: public holiday
