PostgerSQL技术问答06 - Array
本文是《PostgreSQL技术问答》系列文章中的一篇。关于这个系列的由来，可以参阅开篇文章：
文章的编号只是一个标识，在系列中没有明确的逻辑顺序和意义。读者进行阅读时，不用太关注这个方面。
本文的主要内容是关于PostgreSQL中，有关Array(数组)数据类型和实现相关的问题。
什么是Array
Array即为数组。数组在一般的通用编程语言中，是非常基础和常见的特性。但对于关系数据库而言，如何高效的使用存储和处理数组，却是一个有点复杂的问题。因为虽然都是存储和处理一组数据元素，但在一般编程程序的实现中，数组是一个连续的内存空间，数组操作的高性能也是来源于此。而在数据库中，一般的数据是以操作系统文件的形式，保存在磁盘和文件系统之上的，和内存结构其实有很大的区别，访问和操作方式截然不同，所以不能简单的认为它就是一般编程系统中数组的形式。
上面是从技术实现和特性角度的分析，而在应用的角度来看，数组确实提供了数据结构的灵活性。数组的逻辑形式，就是一个有序的相同类型数据的集合，数组中的元素，可以通过位置索引进行快速的访问。这个特性，在一些数据库的应用场景中，也是比较有用的，即使它实际上在技术上失去了一些性能方面的优势。
在Postgres数据库中，我们可以用数组来组织可扩展的相同类型的一组数据，PG还提供了一系列相关的特性，来保证数组操作的方便和快捷。
Postgres如何实现和应用数组操作
数组的本质就是一个有序的集合。所有有序集合相关的操作，Postgre都是可以进行某种形式的支持的。我们可以通过以下一系列主题和要点，来了解在Postgres中，是如何实现数组数据的存储和操作的。
- 字段类型
在创建数据表的时候，就可以声明字段类型使用某种类型的数组。
// 创建表和数组字段
create table adata (id int primary key, ivalues int[], schedule text[][]);
CREATE TABLE
// 插入数据
insert into adata(id,ivalues) values (1, '{1,3,5}'),(2, array[2,4,6]);
INSERT 0 2
// 查询数据
select * from adata;
id | ivalues
----+---------
1 | {1,3,5}
2 | {2,4,6}
(2 rows)
声明字段作为类型数据非常简单，就是类型增加[]描述符，如int[]就可以声明一个整型类型的数组。PG支持多维数组。在声明数组时，可以指定数组的大小，但好像并没有实际的作用，所以一般就是简单的声明就可以了。
- 赋值和呈现
一般情况下，PG支持两种主要的数组赋值和表达形式，字符串和Array关键字。如下面代码所示：
// 数字类型
with A(a) as (values
('{1,4,2}'),
(Array[2,3,4,5])
) select * from A;
// 字符串型
with A(a) as (values
('{"A","B","C"}'), (Array['D','E','F','G'])
) select * from A;
// 字段赋值
update adata set a1= '{1,2}', a2[1] = 3 where id = 1;
字符串形式的数组，表示方式和一般编程语言使用中括号不同，使用单引号和大括号进行包围，数组元素使用逗号分隔。如果是字符串类型的，字符串使用双引号表示。还有一种方式是使用Array关键字，这时的方式就和编程语言很相似了。
如果对数组类型的字段进行赋值，可以选择使用字段赋值或者对于字段数组中的元素进行单独赋值，只需要表述和数据类型对应就可以了。
- 元素引用
这里需要特别注意，PG中的数组使用索引是从1开始的，和一般编程语言从0开始不同。
with A(a) as (values
('{"A","B","C"}'), (Array['D','E','F','G'])
) select a[0] a0, a[1] a1,a[2] a2, a[5] a5 from A;
a0 | a1 | a2 | a5
----+----+----+----
| A | B |
| D | E |
(2 rows)
可以看到，如果引用超过界限，查询语句也不会出错，就是得到空的字段值而已。数组元素引用就和普通数据表字段一样，可以用在很多地方。包括单独赋值、数据关联、条件检查等多个场景。
- 数组的分割和连接
Array的一个常见的操作就是切分成为子数组，以及它的逆操作，将多个数组合并成为一个大数组。如下面的示例：
with A(a1,a2) as (values
(array[1,3,5], '{2,4,6}'::int[]),
(array[7,8,9,10], '{11,12,13}'::int[])
) select array_cat(a1,a2::int[]), a1[1:2], a2[3:4] from A;
array_cat | a1 | a2
---------------------+-------+------
{1,3,5,2,4,6} | {1,3} | {6}
{7,8,9,10,11,12,13} | {7,8} | {13}
(2 rows)
// 使用操作符
ARRAY[1,2,3] || ARRAY[4,5] // [1,2,3,4,5]
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]
可以看到，数组合并，可以使用array_cat方法，PG还支持使用操作符"||"来串接数组。而子数组，可以直接使用上下索引引用方式。有趣的是，数组的上下索引是都是可选的，即可以使用 [2:]，这种方式，获取从第二个元素开始的子数组；也可以使用[:2]这种方式，截取前两个元素作为子数组。
- 数组比较
和字符串一样，虽然不是简单的数值类型，但数组之间，也是可以比较的。笔者理解，数组的比较，基于两个数组之间，逐个对于相同位置的元素进行的比较，并返回第一个不相等的比较结果。多维数组的比较就是单维数组的递归操作。
除了数组的简单比较之外，一个比较复杂的比较关系就是判断两个数组的包含关系。它用于判断一个数组中的所有元素，是否出现在另一个数组中，如果是则认为其被包含。包含的判断和数组元素的位置和是否重复无关。
除了包含关系之外，PG还提供判断两个是否是否“重叠”的操作，即两个数组是否都具有相同值的元素。
对于数组的比较、包含和重叠判断，Postgres提供了一套操作符来进行方便的操作，下面是几个简单的例子：
// 相等
ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3] // true
// 大于小于
ARRAY[1,4,3] > ARRAY[1,2,4] // true
// 包含
ARRAY[1,4,3] @> ARRAY[3,1,3] // true
// 重叠
ARRAY[1,4,3] && ARRAY[2,1] // true
- 数组和字符串的相互转换
可以使用连接字符，将数组转换成为一个字符串；相反的，也可以将一个字符串，基于分隔符，将字符串转换为数组。简单的示例如下：
select array_to_string(array[1,2,3],','), string_to_array('4,5,6',',') ;
array_to_string | string_to_array
-----------------+-----------------
1,2,3 | {4,5,6}
(1 row)
- 数组和记录集的相互转换
PG提供了可以将数组转换为记录集的函数，unnest，原意为取消嵌套。unnest的逆运算是array_agg，即将一个记录集转换成为一个数组。
// 数组转记录
select 'x',unnest(ARRAY[1,2,3,4]) a;
?column? | a
----------+---
x | 1
x | 2
x | 3
x | 4
(4 rows)
// 记录转数组
select array_agg(G) from generate_series(1,5) G;
array_agg
-------------
{1,2,3,4,5}
(1 row)
可以看到，如果unnest和其他字段结合使用，相当于和展开后的单字段记录进行了一次笛卡尔乘积计算，类似于关联的操作。
上面就是PG数组一般常见的应用方式。关于更多的数组操作相关的功能，可以参考PG技术文档中相关的章节。
PG中有那些和数组操作相关的主要特性和函数
PG中的数组操作，通常使用和数组操作相关的内置函数来实现，下面列出了其中一些重点方法，这些内容主要来自PG技术文档，有专门的章节如下：
www.postgresql.org/docs/curren…
在此基础上，笔者做了简单的整理和说明，可以看到，这些函数大多数都是以“array_”作为开头的，所以作用和功能都比较好理解：
- array_cat(anyarray, anyarray):
将两个数组合并成为一个大的数组，参数必须是数组类型。这个操作也可以使用"||"(合并)操作符。此外，array_cat参数支持多维数组，计算时基于最高维度。
- array_append(anyarray, anyelement)，array_prepend(anyelement, anyarray):
将元素扩展到数组后面或者前面，结果是扩展后的数组。
- array_position(anyarray, anyelement [, int])，array_positions(anyarray, anyelement):
查找特定元素在数组中的位置，如果无法找到，则返回空值。array_position查找第一个出现的位置， 而array_positions可以在多个匹配的情况下，返回多个位置，使用数组来表示。
- array_ndims(anyarray),array_ dims(anyarray)
获取数组的维度和维度的文本表示，如 array_dims(ARRAY[[1,2,3], [4,5,6]]) 的结果是"[1:2][1:3]"，表示它是一个二维数组，第一维有两个元素，每个元素又是一个有四个元素的数组。
- array_length(anyarray, int)，cardinality(anyarray)
array_length指定数组和维度的长度。维度参数不能忽略(即使默认为1)。而cardinality可以返回(多维)数组中所有元素(不计算数组自己)的总和。
- array_remove(anyarray, anyelement), array_replace(anyarray, anyelement, anyelement)
从数组中，移除或者替换给定值的元素。可以处理重复元素。只支持一维数组。需要注意，如果强行设置了数组的上下界，其上界可能不为0，而下界也可能不是数组的长度。
- array_upper(anyarray, int)，array_lower(anyarray, int)
返回数组的上界和下界。
- array_to_string(anyarray, text [, text]), string_to_array(text, text [, text])：
前面已经提到，可以在字符串和数组类型之间进行转换。最后那个可选文本参数是处理null的替换文本。
- any(array), all(array)
这两种方法一般不能单独使用，需要在条件判断语句中使用。 如 4 = any(aarry) 或者 2 = all(array)。可以用于判断数组中是否有元素和给定值相等，或者所有的值和给定值都相等等逻辑关系。
- unnest(anyarray)，unnest(anyarray, anyarray [, ...]), array_agg
unnest将数组扩展成为记录集，也支持多个数组的扩展。array_agg基本上是unnest的逆运算，但要注意，array_agg是实际上是一种“聚合函数”，其使用场景可能有一些限制。
如何使用数组数据进行查询
使用数组数据进行查询，通常的情况，就是使用数组中的数据，作为查询条件和依据。由于数组本身的复杂性，可能会涉及几种情况：
- 单一元素条件
这个非常简单，就是使用特定的数组元素参与查询。直接使用索引来方式数组中的元素，就和使用一般字段一样。。
- 数组值查询
这个通常表达某个值和数组之间的关系。这时大致有两种查询逻辑。一是数组中任意元素都符合某种条件，一般使用any()方法，而是数组中所有的元素都符合条件，一般使用all()方法。
- 数组关系查询
可以将两个数组之间的包含和重叠关系，作为查询依据。除了条件相等之外，理论上所有返回值是布尔型的操作符都是可以使用的。
下面的代码我们可以更直观的看到这几种情况的使用方式：
// 数据
with S(id,name,school) as ( values
(1,'关羽','{1,3}'::int[]),
(2,'张飞',array[2,2]),
(3,'赵云',array[4,1,3]),
(4,'马超',array[1,4,2])
)
// 精确查询
select * from S where school[1] = 4
defaultdb-> ;
id | name | school
----+------+---------
3 | 赵云 | {4,1,3}
(1 row)
// 匹配查询
select * from S where 4 = any(school)
;
id | name | school
----+------+---------
3 | 赵云 | {4,1,3}
4 | 马超 | {1,4,2}
(2 rows)
// 全部匹配
select * from S where 2 = all(school);
id | name | school
----+------+--------
2 | 张飞 | {2,2}
(1 row)
// 包含和重叠
with S(id,name,school) as ( values
(1,'关羽','{1,3}'::int[]),
(2,'张飞',array[2,2]),
(3,'赵云',array[4,1,3]),
(4,'马超',array[1,4,2]),
(5,'黄忠',array[1,2,4])
)
select * from S join S S2 on S2.school @> S.school and S2.id <> S.id;
id | name | school | id | name | school
----+------+---------+----+------+---------
1 | 关羽 | {1,3} | 3 | 赵云 | {4,1,3}
2 | 张飞 | {2,2} | 4 | 马超 | {1,4,2}
2 | 张飞 | {2,2} | 5 | 黄忠 | {1,2,4}
4 | 马超 | {1,4,2} | 5 | 黄忠 | {1,2,4}
5 | 黄忠 | {1,2,4} | 4 | 马超 | {1,4,2}
(5 rows)
可以看到，在引入了数组关系的逻辑计算之后，相关的查询条件组合就非常灵活，提供了更多的业务灵活性和计算实现的选择。
有哪些典型应用场景和方式
Postgres提供了如此强大的数组功能，我们如何才能有效的发挥这个特性的作用呢？ 这是我们需要思考的问题。
笔者认为，关系型数据库系统，对数组类型和操作的良好支持，可以极大的提高系统开发和业务应用实现的灵活性。下面是一些笔者在使用和构想中的应用场景，欢迎讨论和补充：
- 操作时间
很多业务应用中，需要记录各种操作和状态的时间，原来的方式，就是使用多个时间戳字段。如果这个需求相对比较明确和固定，完全可以使用数组来解决。可以简化开发，也方便扩展。比如可以为用户信息表设计一个“操作时间”的数组字段，第一个元素是“创建时间”，第二个元素是“修改时间”，第三个元素是“最后登录时间”，就可以方便的使用一个字段，记录和管理用户的各种操作和状态了。
- 操作日志
道理同操作时间。但最好是在明确数据生命周期中，相关的操作流程是有限的，不会造成数据的过分膨胀。
- 对象标签
使用数组，可以不借助外部的标签表，也可以实现记录对象的标签管理。标签的数量和范围一般也是有限的，比较适合这种应用场合。
- 任何序列化同类型数据
在现实的生活和应用的场景中，有很多类似需求。例如，员工的年度工资记录，可以归纳到一个12个元素的数组中，而不需要12条数据表记录。类似的情况还有打卡时间、传感器读数、股票指标数据等等。合理的使用数组来组织和表达数据，可以有效的简化数据库的设计和应用开发。
- 多对多关联
在关系型数据库中，完整的通用的多对多关联关系，通常通过创建一个中间的关联表来进行表达和操作。由于引入了一个中间表，在维护关联关系的时候，需要进行额外的操作。
其实，对于比较简单的，数量比较有限的关联关系，可以考虑使用数组字段来进行处理，这时这个关联关系，就完全限定在记录级别进行数组元素的操作和维护就可以了。后面会举例说明。
能举例说明一下使用数组做多对多关联吗
下面是一个简单的示例代码，表达了学生和学校志愿多对多的关系和查询的方式：
with
H(id,name) as ( values
(1, '北大'),
(2, '清华'),
(3, '上交'),
(4, '复旦')
),
S(id,name,school) as ( values
(1,'关羽','{1,3}'),
(2,'张飞','{2}'),
(3,'赵云','{4,3}'),
(4,'马超','{1,4}')
)
select H.name school,S.name student from S join H on H.id = any(school::int[]) order by H.id;
school | student
--------+---------
北大 | 关羽
北大 | 马超
清华 | 张飞
上交 | 赵云
上交 | 关羽
复旦 | 赵云
复旦 | 马超
(7 rows)
有没有需要注意的问题
笔者感觉肯定是有的。首先是字段中的数组肯定不适合存储非常多的元素，它会影响到数据存储和操作的效率。所以需要仔细评估适用的场景，确定数组的使用是有限和可控的。
另外一个应该就是性能的问题，使用数组中的元素作为查询条件，虽然在Postgres中，也可以使用GIN或GIST进行索引，但应该对性能还是有一定的影响，不如原生字段那么高效。
小结
本文探讨了PostgreSQL中一个非常有趣和有用的特性：数组。研究了它的表示方式，常见的应用操作，相关的函数和操作符，和适用的应用场景等等。
转载自:https://juejin.cn/post/7377202850379186188