PostgreSQL9.2 新增数据类型 Range Type，即范围类型，Range Type 类型的数据可以展现一个范围内的数据，Range Type 可以是多种类型，包括 integer,timestamp 等，Range type 适用于数值，时间需要展现范围的场景，接下来演示 Range Type 的初步应用。

Range Types 数据类型

1. INT4RANGE ― Range of INTEGER
2. INT8RANGE ― Range of BIGINT
3. NUMRANGE ― Range of NUMERIC
4. TSRANGE ― Range of TIMESTAMP WITHOUT TIME ZONE
5. TSTZRANGE ― Range of TIMESTAMP WITH TIME ZONE
6. DATERANGE ― Range of DATE

Range Types Input/Output

• (lower-bound,upper-bound)
• (lower-bound,upper-bound]
• [lower-bound,upper-bound)
  [lower-bound,upper-bound]
• empty

举个例子
(0,100) 不包含0和100，并且包括0到100之间的所有整数
(0,100] 不包含0，包含100,，并且包括0到100之间的所有整数
[0,100) 不包含100，包含0，并且包括0到100之间的所有整数
[0,100] 包括0和100， 并且包括0到100之间的所有整数

Range 类型相关函数

1
2
3
4
5

francs=> select '[1,4]'::int4range;  
int4range  
-----------  
[1,5)  
(1 row)

查询 range 下界

1
2
3
4
5

francs=> select lower('[1,4]'::int4range);  
lower  
-------  
 1  
(1 row)

查询 range 上界

1
2
3
4
5

francs=> select upper('[1,4]'::int4range);  
upper  
-------  
 5  
(1 row)

查询 ramnge 数值是否为空

1
2
3
4
5

francs=> select isempty('[1,4]'::int4range);  
isempty  
---------  
f  
(1 row)

Range 类型应用场景

接下来演示 int4range 类型，比如创建一张学生成绩等级表，分为四部分， 0-60 不及格，60-80 一般，80-90 良，90-100 优秀，下面定义这张表。

创建测试表

1
2
3

francs=> create table test_int4range(id integer primary key,grade int4range,remark varchar(32));  
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_int4range_pkey" for table "test_int4range"  
CREATE TABLE

增加 grade 字段约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14

francs=> alter table test_int4range add exclude using gist (grade with &&);  
NOTICE: ALTER TABLE / ADD EXCLUDE will create implicit index "test_int4range_grade_excl" for table "test_int4range"  
ALTER TABLE

francs=> /d test_int4range;  
 Table "francs.test_int4range"  
Column | Type | Modifiers  
--------+-----------------------+-----------  
id | integer | not null  
grade | int4range |  
remark | character varying(32) |  
Indexes:  
 "test_int4range_pkey" PRIMARY KEY, btree (id)  
 "test_int4range_grade_excl" EXCLUDE USING gist (grade WITH &&)

插入数据

1
2
3
4
5
6
7

francs=> insert into test_int4range values (1,'[0,60)','bad');  
INSERT 0 1
francs=> insert into test_int4range values (2,'[60,80)','just so so');  
INSERT 0 1
francs=> insert into test_int4range values (3,'[70,80)','just so so');  
ERROR: conflicting key value violates exclusion constraint "test_int4range_grade_excl"  
DETAIL: Key (grade)=([70,80)) conflicts with existing key (grade)=([60,80)).

备注：插入数据有重复，引发 conflicting。

1
2
3
4
5
6
7
8
9
10
11
12

francs=> insert into test_int4range values (3,'[80,90)','good');  
INSERT 0 1
francs=> insert into test_int4range values (4,'[90,100)','Excellent');  
INSERT 0 1
francs=> select * From test_int4range;  
id | grade | remark  
----+----------+------------  
 1 | [0,60) | bad  
 2 | [60,80) | just so so  
 3 | [80,90) | good  
 4 | [90,100) | Excellent  
(4 rows)

4.4 查询包括指定元素的记录

1
2
3
4
5
6
7
8
9
10
11

francs=> select * From test_int4range where grade @> 1::int4;  
id | grade | remark  
----+--------+--------  
 1 | [0,60) | bad  
(1 row)
  
francs=> select * From test_int4range where grade @> 75::int4;  
id | grade | remark  
----+---------+------------  
 2 | [60,80) | just so so  
(1 row)

附: Range Types 操作符

参考

• http://www.postgresql.org/docs/9.2/static/rangetypes.html
• http://www.postgresql.org/docs/9.2/static/functions-range.html#RANGE-OPERATORS-TABLE