Range & 复合类型

--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));

-- 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)

-- 类似购物卷包含在有效期
 postgres=# select '[2016-06-01,2016-07-01)'::daterange @> '[2016-06-18,2016-06-18]'::daterange;
 ?column?
----------
 t
(1 row)
postgres=# select '[2016-06-01,2016-07-01)'::daterange @> '[2016-06-18,2016-07-18]'::daterange;
 ?column?
----------
 f

--指定下限，但上限无穷
postgres=# select '[2016-06-01,)'::daterange @> '[2016-06-18,2016-07-18]'::daterange;
 ?column?
----------
 t

-- Extract the upper bound 求范围的上限
postgres=# SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);
 ?column?
----------
 t
(1 row)

-- Compute the intersection 求两个范围的交集
postgres=# SELECT int4range(10, 20) * int4range(15, 25);
 ?column?
----------
 [15,20)
(1 row)

postgres=# SELECT int4range(10, 20) - int4range(15, 25);
 ?column?
----------
 [10,15)
(1 row)

postgres=# SELECT int4range(10, 20) + int4range(15, 25);
 ?column?
----------
 [10,25)
(1 row)

--创建索引
CREATE INDEX reservation_idx ON reservation USING GIST (during);

--pg支持复合类型，注意创建复合类型时 as 不能省略
CREATE TYPE inventory_item AS (
name text,
supplier_id integer,
price numeric
);

CREATE TABLE on_hand (
item inventory_item,
count integer
);
--注意此时的""和null是不一样的
insert into on_hand values('("rudy gao",11,12)' ,1),('("",11,12)' ,2),('(,11,12)' ,3);
insert into on_hand values(row("rudy gao",11,12)) ,4);
--选取name属性为null的值，注意小括号
postgres=# select * from on_hand where (item).name is null;
   item   | count
----------+-------
 (,11,12) |     3
(1 row)
--选取name属性为空值，对于空值要去掉其中双引号
postgres=# select * from on_hand where (item).name = '""';
 item | count
------+-------
(0 rows)
--选取name属性为空值
postgres=# select * from on_hand where (item).name = '';
    item    | count
------------+-------
 ("",11,12) |     2

 --更新复和类型数据值，注意set的左边不能加括号
 update on_hand set item.price=(item).price+1;