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, '[2010-01-01 14:30,"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 worldCREATE INDEX reservation_idx ON reservation USING gist (during);