预订表中仅允许工作时间

admin

预订表中仅允许工作时间

sql

PostgreSQL 9.2保留表定义为

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

表中列出了假期

CREATE TABLE holiday ( day primary key );

工作时间为工作日的8点至18:00，并且预约只能间隔30分钟。如何在期间值中添加约束，以使其仅允许在工作时间内进行保留：

tsrange中的开始日期和结束日期始终相同。
开始和结束日期不能是星期六和星期天
开始日期和结束日期不能显示在公共假期表中
开始时间只能是8:00，8:30，9:00，9:30，… 16：00、16：30、17：00或17:30
结束时间只能是8：30、9：00、9：30，… 16：00、16：30、17：00、17：30或18:00（独家）

将这些约束或某些约束添加到此表是否合理？如果是，如何添加？如果有帮助，可以更改时间表表的结构。

阅读 147

2021-06-07

共1个答案

admin

您需要更改表定义并添加一些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();

最后，进行一些测试：

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

2021-06-07