官方说明 https://www.postgresql.org/docs/9.3/rangetypes.html

Postgres中的range类型可以在一个字段内存储一个范围信息,内置的类型有如下

  • int4range — Range of integer

  • int8range — Range of bigint

  • numrange — Range of numeric

  • tsrange — Range of timestamp without time zone

  • tstzrange — Range of timestamp with time zone

  • daterange — Range of date

除了上述的内置类型, 还可以自定义范围类型

基本用法

CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
    (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');

-- 范围限制
SELECT int4range(10, 20) @> 3;

-- 判断是否有交集
SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);

-- 提取右值
SELECT upper(int8range(15, 25));

-- 提取左值
SELECT lower(int8range(15, 25));

-- 求交集
SELECT int4range(10, 20) * int4range(15, 25);

-- 判断是否为空
SELECT isempty(numrange(1, 5));

开闭区间


Tips: 数学中的开闭区间
设 a,b 是两个实数,且 a ≤ b.
1)满足 a ≤ x ≤ b 的实数 x 的集合,
表示为 [ a,b ],叫做闭区间;
2)满足 a < x <b 的实数 x 的集合,
表示为 ( a,b ),叫做开区间;
3)满足 a ≤ x <b,a <x ≤ b 的实数 x 的集合,
分别表示为 [ a,b ),( a,b ],叫做半开区间.
这里实数 a,b 叫做区间的端点.
从上边的三个定义你就可以看出来,闭区间是有a,b两个端点的.

原则基本和和数学中的开闭区间概念相同,即

  • [] 为闭区间
  • () 为开区间
  • [) 左开右闭
  • (] 左闭右开

无限区间

一个区间的左端点和右端点可以有一个为空,表示无限范围,例如 [2019-01-01, ) 表示2019年1月1日之后的所有日期,也就是说

select daterange('2019-01-01', null) @> '2019-02-01'::date;

会返回True

构造一个range

每一种类型的范围都有对应的构造函数,一般情况下接收两个参数,左值和右值,并且右值必须大于等于左值,除此之外还有第三个参数可以声明这个区间的开闭类型类型,下面是一些例子

-- The full form is: lower bound, upper bound, and text argument indicating
-- inclusivity/exclusivity of bounds.
SELECT numrange(1.0, 14.0, '(]');

-- If the third argument is omitted, '[)' is assumed.
SELECT numrange(1.0, 14.0);

-- Although '(]' is specified here, on display the value will be converted to
-- canonical form, since int8range is a discrete range type (see below).
SELECT int8range(1, 14, '(]');

-- Using NULL for either bound causes the range to be unbounded on that side.
SELECT numrange(NULL, 2.2);

离散范围
离散范围是其元素类型具有明确定义的“step”的范围,例如整数或日期。
在这些类型中,当它们之间没有有效值时,可以说两个元素是相邻的。
这与连续范围形成对比,在连续范围中,总是(或几乎总是)可以识别两个给定值之间的其他元素值。
例如,数字类型上的范围是连续的,时间戳上的范围也是连续的。
(即使时间戳的精度有限,理论上也可以将其视为离散的,因此最好将其视为连续的,因为步长通常不感兴趣。)
考虑离散范围类型的另一种方法是清楚地了解每个元素值的“下一个”或“前一个”值。
知道了,通过选择下一个或前一个元素值而不是最初给定的元素值,可以在范围边界的包含和排他表示之间进行转换。
例如,在整数范围中,类型[4,8]和(3,9)表示相同的值集;
但对于数字范围而言,情况并非如此。
离散范围类型应具有规范化功能,该功能可以识别元素类型所需的步长。
规范化函数负责将范围类型的等效值转换为具有相同的表示,特别是一致的包含或排他限制。
如果未指定规范化函数,则具有不同格式的范围将始终被视为不相等,即使它们实际上可能表示相同的值集。
内置范围类型int4range,int8range和daterange都使用包含下限的规范形式并排除上限( '[)' )。
但是,用户定义的范围类型可以使用其他约定。

创建新的范围类型

除了内置的范围类型外,还可以自定义范围类型,例如下面的例子中创建了 float8 类型的范围

CREATE TYPE floatrange AS RANGE (
    subtype = float8,
    subtype_diff = float8mi
);

SELECT '[1.234, 5.678]'::floatrange;

更多的细节可以查看详细的文档

索引

range类型的字段可以用GiST 或者 SP-GiST来建立索引

CREATE INDEX reservation_idx ON reservation USING gist (during);

range类字段的限制条件

UNIQUE的限制显然不适用于range类型的字段,相对的,我们可以限制数据的区间保持不重叠,如下所示

CREATE TABLE reservation (
    during tsrange,
    EXCLUDE USING gist (during WITH &&)
);

这个限制会将阻止一张表内有相互重叠的区间

INSERT INTO reservation VALUES
    ('[2010-01-01 11:30, 2010-01-01 15:00)');
INSERT 0 1

INSERT INTO reservation VALUES
    ('[2010-01-01 14:45, 2010-01-01 15:45)');
ERROR:  conflicting key value violates exclusion constraint "reservation_during_excl"
DETAIL:  Key (during)=(["2010-01-01 14:45:00","2010-01-01 15:45:00")) conflicts
with existing key (during)=(["2010-01-01 11:30:00","2010-01-01 15:00:00")).

记录一些工作中实际遇到的需求

根据时间范围去join数据

现在有三张表,uses, user_measurements和orders,后面两张都存有users的外键(user_id),先解释下user_measurements这张表,users每天会产生很多更新,而user_measurements就是用来记录这些变化的记录表,机制为每天晚上会为users生成一个新的user_measurements用来记录一个用户当天的情况,而不是最新的情况,user_measurements有一个字段名为during,类型是date_range,里面的左值为该用户昨天的user_measurements的右值,右值为无限。

现在我要搜索某一个订单发生时间内,当时用户的某个状态为某值的订单,也就是说一个用户只能根据订单时间去join一条measurement才能实现这个需求,所以join的条件如下

INNER JOIN user_measurements order_measurement 
on order_measurement.during @> (orders.originally_created_at::TIMESTAMPTZ AT TIME ZONE 'Asia/Shanghai') :: date
AND order_measurement.user_id = orders.user_id

用SQL更新range字段

还是刚才的user_measurements,某天我们发现里面的日期有问题,需要把所有天数都更新为前一天的,SQL如下

update user_measurements
set during = daterange(
        (lower(during) - interval '1 day')::date,
        case
            when upper(during) is null
                then null
            else
                (upper(during) - interval '1 day')::date
            end
    ), updated_at = now()
where lower(during) is not null;