PostgreSQL的INTERVAL HOUR类型列插入分秒不报错如何解决

你当前使用的INTERVAL HOUR类型仅控制显示输出的精度，底层存储仍然会保留分钟、秒的完整精度，原有约束只校验了时间区间范围，没有对非小时部分做限制，因此带分钟/秒的合法区间值可以正常插入。

方案1：保留INTERVAL类型，修改CHECK约束 #

直接在原有约束基础上新增对分钟、秒的校验规则，确保二者均为0：

-- 删除原有约束
ALTER TABLE toto DROP CONSTRAINT IF EXISTS check_period;
-- 新增同时校验区间和整小时规则的约束
ALTER TABLE toto ADD CONSTRAINT check_period CHECK (
    period BETWEEN INTERVAL '0 hour' AND INTERVAL '48 hour'
    AND EXTRACT(MINUTE FROM period) = 0
    AND EXTRACT(SECOND FROM period) = 0
);

校验逻辑说明：通过EXTRACT函数提取interval中的分钟、秒数值，只要有一个不为0，约束就会校验失败，数据库直接抛出错误，拒绝操作。

方案2：替换为数值类型存储（性能更优） #

如果业务不需要直接存储interval类型，可以直接用小整型存储小时数，从根源上避免非整小时输入：

-- 替换原有period字段
ALTER TABLE toto DROP COLUMN IF EXISTS period;
ALTER TABLE toto ADD COLUMN period SMALLINT DEFAULT 24 CHECK (period BETWEEN 0 AND 48);

-- 读取需要interval类型时，可通过运算转换
SELECT period * INTERVAL '1 hour' AS period FROM toto;

内容的提问来源于stack exchange，提问作者Jimmy Gonçalves