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