PostgreSQL数据类型——范围类型

PostgreSQL数据类型——范围类型

实际工作中并不是所有业务都会使用这些类型，但是在查询数据过程中过滤某些数据的时候，我们可以将一些条件，转换成这些特殊的数据格式，通过这些数据格式对应的函数，来实现一些复杂条件才能实现的过滤。

版本为9.6版本

在PostgreSQL中目前支持六种区间范围

类型	说明
int4range	integer范围类型
int8range	bigint范围类型
numrange	numeric范围类型
tsrange	不带时区的timestamp范围类型
tstzrange	带时区的timestamp范围类型
daterange	date范围类型

定义范围类型

将一个字段设置为数组类型只需要在其基本类型后添加[]即可。

create table test_rang (
	id integer,
	rang_int int4range,
	rang_date daterange
)

如何插入范围类型数据

插入数据时[ 表示包含下界，(表示不包含下界 ，]表示包含上界，)不包含上界。需要注意即使设置了包含上界，但是在最终依然会显示为 )。这是范围类型的标准的边界模式。

INSERT INTO public.test_rang
(id, rang_int, rang_date)
VALUES(2, '[1,3]', '["2022/10/01","2022/11/01"]');
## 插入内容
2	[1,4)	[2022-10-01,2022-11-02)



INSERT INTO public.test_rang
(id, rang_int, rang_date)
VALUES(3, '(1,3)', '("2022/10/01","2022/11/01")');
## 插入内容
3	[2,3)	[2022-10-02,2022-11-01)

插入一个开放的范围

如果我们在设置数据的时候只设置其中一个参数，我们会得到一个没有无边界的范围数据

INSERT INTO public.test_rang
(id, rang_int, rang_date)
VALUES(1, '[1,]', '["2022/10/01",]');
## 插入内容
1	[2,)	[2022-10-02,)



INSERT INTO public.test_rang
(id, rang_int, rang_date)
VALUES(2, '(,3)', '(,"2022/11/01")');
## 插入内容
2	(,3)	(,2022-11-01)

范围类型的操作符

操作符	描述	例子	结果
=	等于	int4range(1,5) = '[1,4]'::int4range	true
<>	不等于	numrange(1.1,2.2) <> numrange(1.1,2.3)	true
<	小于	int4range(1,10) < int4range(2,3)	true
>	大于	int4range(1,10) > int4range(1,5)	true
<=	小于或等于	numrange(1.1,2.2) <= numrange(1.1,2.2)	true
>=	大于或等于	numrange(1.1,2.2) >= numrange(1.1,2.0)	true
@>	包含范围	int4range(2,4) @> int4range(2,3)	true
@>	包含元素	'[2011-01-01,2011-03-01)'::tsrange @> '2011-01-10'::timestamp	true
<@	范围包含于	int4range(2,4) <@ int4range(1,7)	true
<@	元素包含于	42 <@ int4range(1,7)	false
&&	重叠 (有共同点)	int8range(3,7) && int8range(4,12)	true
<<	严格在左	int8range(1,10) << int8range(100,110)	true
>>	严格在右	int8range(50,60) >> int8range(20,30)	true
&<	没有延伸到右边	int8range(1,20) &< int8range(18,20)	true
&>	没有延伸到左边	int8range(7,20) &> int8range(5,10)	true
- | -	相邻	numrange(1.1,2.2) -|- numrange(2.2,3.3)`	true
+	并集	numrange(5,15) + numrange(10,20)	[5,20)
*	交集	int8range(5,15) * int8range(10,20)	[10,15)
-	差集	int8range(5,15) - int8range(10,20)	[5,10)

范围相关函数

转换

将普通数据转换为范围数据有两种方式。

1. 将转换目标的类型格式用()将数据包括起来，类似这样int8range(1,10)
2. 使用:: 对数据进行转换，类似这样'(1,10)'::int8range，但是需要注意此时()标识的数据无上下边界

## 使用这种模式最终输出的是包含1的下界
select int8range(1,10)
获取结果 [1,10)

select '(1,10)'::int8range
获取结果 [2,10)

结果获取

这部分函数是用来获取范围数据中的某些值的内容。

函数	返回类型	描述	示例	结果
lower(anyrange)	范围元素类型	范围的下界	lower(numrange(1.1,2.2))	1.1
upper(anyrange)	范围元素类型	范围的上界	upper(numrange(1.1,2.2))	2.2

对于无边界的数据它获取的内容为NULL。

## 获取的结果
select id,lower(rang_int),lower(rang_date) from test_rang
1	2	2022-10-02
2	NULL NULL	

select id,upper(rang_int),upper(rang_date) from test_rang
1	NULL NULL	
2	3	2022-11-01

值判断

这部分函数用来对范围数据进行一个判断用来获取范围数据是否符合一些规定。但是实际工作用其不仅仅是用来进行范围数据的判断，我们可以将SQL查询出来的内容转换成范围数据进行函数判断，通过这样可以对普通的数据进行一些范围数据才有的操作。

函数	返回类型	描述	示例	结果
isempty(anyrange)	boolean	范围是空的	isempty(numrange(1.1,2.2))	false
lower_inc(anyrange)	boolean	包涵下界	lower_inc(numrange(1.1,2.2))	true
upper_inc(anyrange)	boolean	包含上界	upper_inc(numrange(1.1,2.2))	false
lower_inf(anyrange)	boolean	下界无穷	lower_inf('(,"2022/11/01")'::daterange)	true
upper_inf(anyrange)	boolean	上界无穷	upper_inf('("2022/11/01",)'::daterange)	tru