PostgreSQL中daterange[]类型日期冲突检测与更新问题

针对你使用daterange[]类型存储别墅预订日期的场景，下面直接给出冲突检测、无冲突添加、冲突合并的完整SQL实现：

一、冲突检测 #

先判断新的预订范围是否与已有范围重叠：

-- 替换p_villa_id、p_check_in、p_check_out为实际值
SELECT EXISTS (
    SELECT 1
    FROM villas
    WHERE id = p_villa_id
    AND EXISTS (
        SELECT 1
        FROM unnest(reserved_dates) AS r
        -- [) 模式和你手动插入的格式一致，退房日期不包含在内
        WHERE r && daterange(p_check_in, p_check_out, '[)')
    )
);

• unnest(reserved_dates)：将日期范围数组拆分为单个范围行，方便逐行检查
• &&：PostgreSQL范围类型的重叠判断操作符，只要两个范围有交集就返回true

二、无冲突时添加新预订 #

直接将新范围追加到数组末尾：

UPDATE villas
SET reserved_dates = array_append(reserved_dates, daterange(p_check_in, p_check_out, '[)'))
WHERE id = p_villa_id
-- 仅当无冲突时执行
AND NOT EXISTS (
    SELECT 1
    FROM unnest(reserved_dates) AS r
    WHERE r && daterange(p_check_in, p_check_out, '[)')
);

三、有冲突时合并重叠范围 #

当存在重叠时，把所有重叠的旧范围和新范围合并成一个连续范围，保留不重叠的旧范围，最终更新数组：

UPDATE villas
SET reserved_dates = (
    -- 保留所有不与新范围重叠的旧范围
    SELECT array_agg(r)
    FROM unnest(reserved_dates) AS r
    WHERE NOT r && daterange(p_check_in, p_check_out, '[)')
) || array[
    -- 合并所有重叠的旧范围和新范围为一个连续范围
    (SELECT range_merge(
        daterange(p_check_in, p_check_out, '[)'),
        range_agg(r)
    )
    FROM unnest(reserved_dates) AS r
    WHERE r && daterange(p_check_in, p_check_out, '[)'))
]
WHERE id = p_villa_id
-- 仅当有冲突时执行
AND EXISTS (
    SELECT 1
    FROM unnest(reserved_dates) AS r
    WHERE r && daterange(p_check_in, p_check_out, '[)')
);

• range_agg(r)：将所有重叠的旧范围聚合成一个范围集合
• range_merge：把新范围和聚合后的重叠范围合并成一个连续的大范围
• ||：数组拼接操作符，将不重叠的旧范围数组和合并后的新范围数组拼接

四、封装成函数（推荐） #

把上面的逻辑封装成PL/pgSQL函数，调用更便捷：

CREATE OR REPLACE FUNCTION update_villa_reservations(
    p_villa_id INT,
    p_check_in DATE,
    p_check_out DATE
) RETURNS VOID AS $$
DECLARE
    v_new_range daterange := daterange(p_check_in, p_check_out, '[)');
    v_has_conflict BOOLEAN;
BEGIN
    -- 先检测冲突状态
    SELECT EXISTS (
        SELECT 1
        FROM unnest((SELECT reserved_dates FROM villas WHERE id = p_villa_id)) AS r
        WHERE r && v_new_range
    ) INTO v_has_conflict;

    IF v_has_conflict THEN
        -- 处理冲突：合并范围
        UPDATE villas
        SET reserved_dates = (
            SELECT array_agg(r)
            FROM unnest(reserved_dates) AS r
            WHERE NOT r && v_new_range
        ) || array[
            (SELECT range_merge(v_new_range, range_agg(r))
             FROM unnest(reserved_dates) AS r
             WHERE r && v_new_range)
        ]
        WHERE id = p_villa_id;
    ELSE
        -- 无冲突：添加新范围
        UPDATE villas
        SET reserved_dates = array_append(reserved_dates, v_new_range)
        WHERE id = p_villa_id;
    END IF;
END;
$$ LANGUAGE plpgsql;

调用示例：

-- 给ID为1的别墅添加/合并2023-02-10至2023-02-20的预订
SELECT update_villa_reservations(1, '2023-02-10', '2023-02-20');

这个方案能处理所有重叠场景：新范围包含旧范围、旧范围包含新范围、部分重叠，都会自动合并成一个连续的日期范围。

内容的提问来源于stack exchange，提问作者Eyyüp Ensar Özcan