PostgreSQL数据类型——数组类型
实际工作中并不是所有业务都会使用这些类型，但是在查询数据过程中过滤某些数据的时候，我们可以将一些条件，转换成这些特殊的数据格式，通过这些数据格式对应的函数，来实现一些复杂条件才能实现的过滤。
版本为9.6版本
在PostgreSQL中每种数据类型都有相应的基础的数组类型
定义数组类型
将一个字段设置为数组类型只需要在其基本类型后添加[]即可。
create table test_arr (
id integer,
arr_int integer[],
arr_text text[]
)
如何插入数组类型
数组类型数据插入支持两种方式。
- 使用
{}包裹数组内容，使用,对元素进行分割。类型下面样子
insert into test_arr (id,arr_int,arr_text)
values(1,'{1,2,3}','{"test1","test2","test3"}')
- 使用
array将数据转换为数组
insert into test_arr (id,arr_int,arr_text)
values(2,array[1,2,3],array['test1','test2','test3'])
如何查询数组类型
数组字段支持在字段后面添加索引来查询数组中指定索引的数据类型这样。如果指定索引超过数组长度，则返回null。
select arr_int[1],arr_text[2],arr_text[4] from test_arr
数组类型操作符
PostgreSQL 提供了下面的操作类实现数组间的处理或者判断
| 操作符 | 描述 | 例子 | 结果 |
|---|---|---|---|
= |
等于 | ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3] |
true |
<> |
不等于 | ARRAY[1,2,3] <> ARRAY[1,2,4] |
true |
< |
小于 | ARRAY[1,2,3] < ARRAY[1,2,4] |
true |
> |
大于 | ARRAY[1,4,3] > ARRAY[1,2,4] |
true |
<= |
小于或等于 | ARRAY[1,2,3] <= ARRAY[1,2,3] |
true |
>= |
大于或等于 | ARRAY[1,4,3] >= ARRAY[1,4,3] |
true |
@> |
包含 | ARRAY[1,4,3] @> ARRAY[3,1] |
true |
<@ |
被包含于 | ARRAY[2,7] <@ ARRAY[1,7,4,2,6] |
true |
&& |
重叠(有共同元素) | ARRAY[1,4,3] && ARRAY[2,1] |
true |
| || | 数组与数组连接 | ARRAY[1,2,3] || ARRAY[4,5,6] | {1,2,3,4,5,6} |
| || | 数组与数组连接 | ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]] | {{1,2,3},{4,5,6},{7,8,9}} |
| || | 元素与数组连接 | 3 || ARRAY[4,5,6] | {3,4,5,6} |
| || | 数组与元素连接 | ARRAY[4,5,6] || 7 | {4,5,6,7} |
数组相关函数
PostgreSQL 提供了一些的函数来帮助我们对数组进行处理
内容的修改
这些内容主要是针对数组类型的返回结果进行处理
| 函数 | 说明 | 语句 | 结果 |
|---|---|---|---|
| array_append | 向数组的末尾添加元素 | array_append(ARRAY[1,2], 3) | {1,2,3} |
| array_prepend | 向数组的开头添加函数 | array_prepend(1, ARRAY[2,3]) | {1,2,3} |
| array_cat | 连接两个数组 | array_cat(ARRAY[1,2,3], ARRAY[4,5]) | {1,2,3,4,5} |
| array_replace | 用新值替换每个等于给定值的数组元素 | array_replace(ARRAY[1,2,5,4], 5, 3) | {1,2,3,4} |
| [start:end] | [2:4] | select arr_text[1:2] from test_arr | {‘test2’,‘test3’} |
数组信息查询
这部分函数主要是对数组数据的源数据进行查询
| 函数 | 说明 | 语句 | 结果 |
|---|---|---|---|
| array_dims | 返回数组维数的文本表示 | array_dims(ARRAY[[1,2,3], [4,5,6]]) | [1:2][1:3] |
| array_lower | 返回数组维数的下界 | array_lower(‘[0:2]={1,2,3}’::int[], 1) | 0 |
| array_upper | 返回数组维数的上界 | array_upper(ARRAY[1,8,3,7], 1) | 4 |
| array_ndims | 返回数组的维数 | array_ndims(ARRAY[[1,2,3], [4,5,6]]) | 2 |
| array_length | 返回数组维度的长度 | array_length(array[1,2,3], 1) | 3 |
| array_position | 数组中指定元素出现的位置 | array_position(ARRAY[1,8,3,7], 8) | 2 |
| cardinality | 返回数组中的总元素数量，或者如果数组是空的则为0 | select cardinality(ARRAY[1,2,3,5]) | 4 |
转换
这里面的函数提供了数组和字符串的转换，在实际应用中，这部分的逻辑使用的会比较多。很多时候一些查询中我们数据类型是个字符串，但是符合数组的结构特征，我们希望以数组的特性去操作这个字符串，这里面的函数无疑是必要的。
| 函数 | 说明 | 语句 | 结果 |
|---|---|---|---|
| array_to_string | 将数组转换为字符串，使用分隔符和null字符串连接数组元素 | select array_to_string(ARRAY[1, 2, 3, NULL, 5], ‘,’, ‘N’) | 1,2,3,N,5 |
| string_to_array | 使用指定的分隔符和null字符串把字符串分裂成数组元素 | string_to_array(‘A,B,C,D’,‘,’) | {A,B,C,D} |
结果转换
这部分的参数，会将数组结果进行合并和展开。
| 函数 | 说明 | 语句 | 结果 |
|---|---|---|---|
| array_agg | 把多个值合并到一个数组中 | SELECT case_id, array_agg(case_label) as case_label FROM case_label WHERE group by case_id | [1:2][1:3] |
| unnest | 扩大一个数组为一组行 | select unnest(ARRAY[1,2,3,4]) | 1 2 3 4 |
unnest
如果一个查询中多个字段使用unnest，则每行都会被展开类似这样
select unnest(ARRAY[1,2,3,4]), unnest(ARRAY['A','B','C','D'])
1 A
2 B
3 C
4 D
但是如果数组元素数量不一致，会出现类似笛卡儿积的结果
select unnest(ARRAY[1,2,3,4]), unnest(ARRAY['A','B','C'])
1 A
2 B
3 C
4 A
1 B
2 C
3 A
4 B
1 C
2 A
3 B
4 C