postgresql数据类型

--pg支持范围类型
? 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)');
 --正无空大，负无穷小 -infinity 代表无穷小
 INSERT INTO reservation VALUES
(1109,"infinity" )'),(1110,'["-infinity",2010-01-01 14:30)');
postgres=# select * from reservation ;
 room |                    during                     
------+-----------------------------------------------
 1108 | ["2010-01-01 14:30:00","2010-01-01 15:30:00")
 1109 | ["2010-01-01 14:30:00",infinity)
 1110 | [-infinity,"2010-01-01 14:30:00")
 
 -- Containment 范围内是否包含某一个值
SELECT int4range(10,20) @> 3;
-- Overlaps 两个范围是否有重叠
SELECT numrange(11.1,22.2) && numrange(20.0,30.0);
-- Extract the upper bound 求范围的上限
SELECT upper(int8range(15,25));
-- Compute the intersection 求两个范围的交集
SELECT int4range(10,20) * int4range(15,25);
-- Is the range empty? 判断范围是否为空
SELECT isempty(numrange(1,5));

--每个范围类型都有一个与对应的构造函数，注意第三个参数说明其是全包围还是半包围
postgres=# SELECT int8range(1,14,'(]');
 int8range 
-----------
 [2,15)
postgres=# SELECT numrange(NULL,2.2);
 numrange 
----------
 (,2.2)
 
--用户也可以自定义范围类型，注意如果想要更好的使用GiST or SP-GiST索引，则需要定义一个差异化函数
--差异化函数要返回一个float8的值，并且其结果不能受字符集和排序规则的影响
--The subtype difference function takes two input values of the subtype,and returns their difference (i.e.,X minus Y) represented as a float8 value
--the subtype_diff function should agree with the sort ordering implied by the selected operator class and collation

--创建差异化函数
CREATE FUNCTION time_subtype_diff(x time,y time) RETURNS float8 AS
'SELECT EXTRACT(EPOCH FROM (x - y))' LANGUAGE sql STRICT IMMUTABLE;
--创建自定义的范围类型
CREATE TYPE timerange AS RANGE (
subtype = time,subtype_diff = time_subtype_diff
);

postgres=# SELECT '[11:10,23:00]'::timerange;
      timerange      
---------------------
 [11:10:00,23:00:00]
 
 
 --可以对范围类型的表列创建 GiST 和 SP-GiST 索引。
 --虽然对范围类型的表列可以创建 B-tree 和哈希索引，但不建议使用
 --There is a B-tree sort ordering defined for range values,with corresponding < and > operators,but the ordering is rather arbitrary and not usually useful in the real world
CREATE INDEX reservation_idx ON reservation USING gist (during);

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!