PostgreSQL如何判断整数是否在二维数组列的任意整数对区间内

筛选符合条件的表记录 #

使用generate_subscripts遍历二维数组第一维下标，配合EXISTS判断是否存在满足区间条件的元素对即可，完整SQL如下：

SELECT schedule."hours"
FROM public."Schedule" AS schedule
WHERE EXISTS (
    SELECT 1
    -- 获取二维数组第一维的所有有效下标
    FROM generate_subscripts(schedule."hours", 1) AS sub(idx)
    -- 校验当前下标对应的区间是否包含目标值
    WHERE 4200 BETWEEN schedule."hours"[sub.idx][1] AND schedule."hours"[sub.idx][2]
);

生成预期格式的校验结果 #

如果需要直接输出指定数值的匹配结果，可使用以下SQL：

WITH test_input(val) AS (
    VALUES (4200), (900), (4501), (8000)
),
mock_array(arr) AS (
    VALUES ('{{1000,3000},{4000,4500},{8000,9000}}'::int[][])
)
SELECT 
    t.val || ' = ' || CASE WHEN EXISTS (
        SELECT 1
        FROM generate_subscripts(a.arr, 1) AS sub(idx)
        WHERE t.val BETWEEN a.arr[sub.idx][1] AND a.arr[sub.idx][2]
    ) THEN 'true' ELSE 'false' END AS result
FROM test_input t, mock_array a;

输出结果完全匹配预期示例：

4200 = true
900 = false
4501 = false
8000 = true

实现说明 #

generate_subscripts(array, dimension)函数会返回指定数组对应维度的所有合法下标，这里我们遍历二维数组第一维（即每个区间对的位置下标），逐一校验区间是否包含目标值，只要存在任意一个符合要求的区间，EXISTS子句就返回true，对应记录就会被筛选出来。

内容的提问来源于stack exchange，提问作者lemongrab