除了第3项之外,你可以用简单的方法解决所有问题CHECK限制条件 https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS:
使用专用范围函数 lower(anysrange) and upper(anyrange) https://www.postgresql.org/docs/current/functions-range.html#RANGE-FUNCTIONS-TABLE访问范围的下限/上限。
1.) tsrange 中的开始日期和结束日期始终相同。
CONSTRAINT schedule_same_day
CHECK (lower(during)::date = upper(during)::date)
2.) 开始和结束日期不能是周六和周日
Use isodow, not dow https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT为了更简单的表达。
CONSTRAINT schedule_no weekend
CHECK (EXTRACT(ISODOW FROM lower(during)) < 6) -- upper is on same day
3.) 开始和结束日期不能出现在公共假期表中
为此你需要一个trigger like:
CREATE OR REPLACE FUNCTION trg_during_no_holy()
RETURNS trigger
LANGUAGE plpgsql AS
$func$
BEGIN
IF EXISTS (SELECT FROM holiday WHERE day = lower(NEW.during)) THEN
RAISE EXCEPTION 'Day too holy: %', lower(NEW.during);
END IF;
RETURN NEW;
END
$func$;
CREATE TRIGGER insupbef_holycheck
BEFORE INSERT OR UPDATE
ON schedule
FOR EACH ROW
EXECUTE PROCEDURE trg_during_no_holy();
4.) 开始时间只能为 8:00、8:30、9:00、9:30、...、16:00、16:30、17:00 或 17:30(含)。
5.) 结束时间只能是 8:30、9:00、9:30、...、16:00、16:30、17:00、17:30 或 18:00(不含)。
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.
)
更新:在Postgres 14或较新的用途date_bin()
:
CONSTRAINT schedule_8_inc_to_18_exc_half_hours CHECK (
lower(during) < upper(during)
AND lower(during)::time >= time '08:00'
AND upper(during)::time <= time '18:00'
AND during = date_bin('30 min', during, '2000-01-01') -- only :00 or :30 exactly
AND lower_inc(during) -- lower bound always incl.
AND upper_inc(during) = false -- upper bound always excl.
)
这也强制执行您的附加要求“限制秒和小数秒”.
See:
- 在 Postgres 中将时间戳截断为 5 分钟的最快方法是什么? https://stackoverflow.com/questions/7299342/what-is-the-fastest-way-to-truncate-timestamps-to-5-minutes-in-postgres/75160734#75160734