PostgreSQL修炼之道：从小工到专家（第2版）最新章节_唐成著

5.15　数组类型

PostgreSQL支持表的字段使用定长或可变长度的一维或多维数组，数组的类型可以是任何数据库内建的类型、用户自定义的类型、枚举类型及组合类型。但目前还不支持domain类型。

5.15.1　数组类型的声明

现举例说明数组类型是如何声明的：

CRETE TABLE testtab04(id int, col1 int[], col2 int[10], col3 text[][]);

从上面的例子中可以看出，数组类型的定义就是通过在数组元素类型名后面附加方括号“[]”来实现的。方括号中可以给一个长度数字，也可以不给，同时也可以定义多维数组。多维数组是通过加多对方括号来实现的。实际上，在目前的PostgreSQL实现中，如果在定义数组类型中填一个数组长度的数字，这个数字是无效的，不会限制数组的长度；定义时指定数组维度也是没有意义的，数组的维度实际上也是根据实际插入的数据来确定的。也就是说，下面两个声明的意义是相同的。

CRETE TABLE testtab04(id int, col1 int[], col2 int[10], col3 text[][]);
CRETE TABLE testtab04(id int, col1 int[10], col2 int[], col3 text[]);

在第一个语句中第二列的声明“col2 int[10]”与在第二个语句中第二列的声明“col2 int[]”的意义是相同的；而在第一个语句中第三列的声明“col3 text[][]”与在第二个语句中第三列的声明“col3 text[]”的意义也是相同的。

5.15.2　如何输入数组值

下面通过几个例子来说明如何输入数组类型的数据，示例如下：

osdba=# create table testtab05(id int, col1 int[]);
CREATE TABLE
osdba=# insert into testtab05 values(1,'{1,2,3}');
INSERT 0 1
osdba=# insert into testtab05 values(2,'{4,5,6}');
INSERT 0 1
osdba=# select * from testtab05;
 id |  col1   
----+---------
  1 | {1,2,3}
  2 | {4,5,6}
(2 rows)

上面的例子是输入一个整数类型的数组，那么字符串类型的数组数据又应如何输入呢？示例如下：

osdba=# create table testtab06(id int, col1 text[]);
CREATE TABLE
osdba=# insert into testtab06 values(1,'{how,howe,howl}');
INSERT 0 1

从上面的例子中可以看出，数组的输入值是使用单引号加大括号来表示的。各个元素值之间是用逗号分隔的。实际上，是否使用逗号分隔各个元素值与元素类型有关，在PostgreSQL中，每个类型都定义的分隔符如下：

osdba=# select typname, typdelim from pg_type where typname in ('int4','int8','bool','char','box');
 typname | typdelim 
---------+----------
 bool    | ,
 char    | ,
 int8    | ,
 int4    | ,
 box     | ;
(5 rows)

在PostgreSQL中，除了box类型的分隔符为分号以外，其他的类型基本上都使用逗号作为分隔符。

box类型使用分号作为分隔符，示例如下：

osdba=# create table testtab08(id int, col1 box[]);
CREATE TABLE
osdba=# insert into testtab08 values(1, '{((1,1),(2,2)); ((3,3),(4,4)); ((1,2),(7,9))}');
INSERT 0 1
osdba=# select * from testtab08;
 id |                 col1                  
----+---------------------------------------
  1 | {(2,2),(1,1);(4,4),(3,3);(7,9),(1,2)}
(1 row)

上面输入的字符串内容中是没有空格的，在有空格时，又该如何输入呢？示例如下：

osdba=# insert into testtab06 values(2,'{how many,how mach,how old}');
INSERT 0 1

从上面的示例中可以看到，有空格也可以直接输入。

那么字符串中有逗号时怎么办呢？这时可以使用双引号，示例如下：

osdba=# insert into testtab06 values(4,'{"who, what", "CO.,LTD."}');
INSERT 0 1

如果字符串中有单引号怎么办呢？这时可以使用两个连接的单引号表示一个单引号：

osdba=# insert into testtab06 values(3,'{"who''s bread", "It''s ok"}');
INSERT 0 1

如果输入的字符串中有括号“｛”和“｝”怎么办呢？只需要把它们放到双引号中即可：

osdba=# insert into testtab06 values(5,'{"{os,dba}", "{dba,os}"}');
INSERT 0 1

如果输入的字符串中有双引号怎么办呢？需要在双引号前加反斜扛，示例如下：

osdba=# insert into testtab06 values(6,'{os\"dba}');
INSERT 0 1

要将一个数组元素的值设为“NULL”，直接写上“NULL”即可（与大小写无关）。要将一个数组元素的值设为字符串“"NULL"”，那么就必须加上双引号。

除了上面介绍的方法以外，还可以使用ARRAY构造器语法输入数据，数组构造器是一个表达式，它从自身的成员元素上构造一个数组值。简单的数组构造器由关键字“ARRAY”、一个左方括弧“[”、一个或多个表示数组元素值的表达式（用逗号分隔）、一个右方括弧“]”组成。示例如下：

osdba=# insert into testtab06 values(6,ARRAY['os','dba']);
INSERT 0 1
osdba=# insert into testtab06 values(6,ARRAY['os"dba','123"456']);
INSERT 0 1
osdba=# insert into testtab06 values(6,ARRAY['os''dba','123''456']);
INSERT 0 1

多维数组的示例如下：

osdba=# create table testtab07(id int, col1 text[][]);
CREATE TABLE
osdba=# insert into testtab07 values(1,ARRAY[['os','dba'],['dba','os']]);
INSERT 0 1

在向多维数组中插入值时，各维度元素的个数必须相同，否则会报如下错误：

osdba=# insert into testtab07 values(2, '{{a,b},{c,d,e}}');
ERROR:  multidimensional arrays must have array expressions with matching dimensions
LINE 1: insert into testtab07 values(2, '{{a,b},{c,d,e}}');
                                        ^

上面的第一个“{a,b}”中有两个元素，而第二个“{c,d,e}”中有3个元素，这是不行的，元素个数必须相同，此时就得补空值，SQL语句如下：

osdba=# insert into testtab07 values(2, '{{a,b,null},{c,d,e}}');
INSERT 0 1
osdba=# select * from testtab07;
 id |         col1         
----+----------------------
  2 | {{a,b,NULL},{c,d,e}}
(1 row)

默认情况下，PostgreSQL数据库中数组的下标是从1开始的，但也可以指定下标的开始值，示例如下：

osdba=# create table test02(id int[]);
CREATE TABLE
osdba=# insert into test02 values('[2:4]={1,2,3}');
INSERT 0 1
osdba=# select id[2],id[3],id[4] from test02;
 id | id | id 
----+----+----
  1 |  2 |  3
(1 row)

从上面的例子中可以看出，指定数组上下标的格式如下：

'［下标：上标］=[元素值1，元素值2，元素值3，....]'

5.15.3　访问数组

访问数组的示例如下：

osdba=# create table testtab08(id int, col1 text[]);
CREATE TABLE
osdba=# insert into testtab08 values(1,'{aa,bb,cc,dd}');
INSERT 0 1
osdba=# insert into testtab08 values(2,'{ee,ff,gg,hh}');
INSERT 0 1
osdba=# select * from testtab08;
 id |     col1      
----+---------------
  1 | {aa,bb,cc,dd}
  2 | {ee,ff,gg,hh}
(2 rows)
osdba=# select id, col1[1] from testtab08;
 id | col1 
----+------
  1 | aa
  2 | ee
(2 rows)

从上面的示例中可以看出，访问数组中的元素时在方括号内加数字就可以了，就像C语言中一样。但需要注意的是，在PostgreSQL中，数组的下标默认是从1开始的，而不是像C语言中从0开始，当然也可以创建从0（实际可以是任意数字）开始的数组，示例如下：

osdba=# create table test02(id int[]);
CREATE TABLE
osdba=# insert into test02 values('[0:2]={1,2,3}');
INSERT 0 1
osdba=# select id[0],id[1],id[2] from test02;
 id | id | id 
----+----+----
  1 |  2 |  3
(1 row)

还可以使用数组切片，示例如下：

osdba=# select id, col1[1:2] from testtab08;
 id |  col1   
----+---------
  1 | {aa,bb}
  2 | {ee,ff}
(2 rows)

二维数组的访问示例如下：

osdba=# create table testtab09(id int, col1 int[][]);
CREATE TABLE

osdba=# insert into testtab09 values(1,'{{1,2,3},{4,5,6},{7,8,9}}');
INSERT 0 1
osdba=# select id,col1[1][1],col1[1][2],col1[2][1],col1[2][2] from testtab09;
 id | col1 | col1 | col1 | col1 
----+------+------+------+------
  1 |    1 |    2 |    4 |    5
(1 row)

在对二维数组进行访问时，如果只使用一个下标是否能返回其中一维的全部元素？示例如下：

osdba=# select id,col1[1] from testtab09;
 id | col1 
----+------
  1 |     
(1 row)

从上面的运行结果来看，答案是不行的，其返回结果为空。实际上，如果想返回多维数组中某一维的全部元素，可以使用切片，切片的起始位置与结束位置相同即可，示例如下：

osdba=# select id,col1[1:1] from testtab09;
 id |   col1    
----+-----------
  1 | {{1,2,3}}
(1 row)

当我们把单个下标和切片混用时，下面的结果是否会让人看不懂？

osdba=# select id, col1[3][1:2] from testtab09;
 id |        col1         
----+---------------------
  1 | {{1,2},{4,5},{7,8}}
(1 row)

以一般的理解来看，“col1[3]”表示“{7,8,9}”，然后取切片“[1:2]”应该返回“{7,8}”，为何返回的是“{{1,2},{4,5},{7,8}}”呢？原来PostgreSQL中规定，只要出现一个冒号，其他的单个下标隐含表示的都是从1开始的切片，下标的数据表示切片的结束值，“col1[3][1:2]”中的“col[3]”实际上表示的是“col1[1:3]”，这样这个表达式实际上就是“col1[1:3][1:2]”了，如此得到这样的结果也就很好理解了。同样，也更容易理解下面的结果：

osdba=# select id, col1[1:2][2] from testtab09;
 id |     col1      
----+---------------
  1 | {{1,2},{4,5}}
(1 row)

其中“col1[1:2][2]”实际上等价于“col1[1:2][1:2]”。

5.15.4　修改数组

数组值可以整个被替换，也可以只替换数组中的单个元素。

替换整个数组值的示例如下：

osdba=# select * from testtab09;
 id |           col1            
----+---------------------------
  1 | {{1,2,3},{4,5,6},{7,8,9}}
(1 row)
osdba=# update testtab09 set col1='{{10,11,12},{13,14,15},{16,17,18}}' where id=1;
UPDATE 1
osdba=# select * from testtab09;
 id |                col1                
----+------------------------------------
  1 | {{10,11,12},{13,14,15},{16,17,18}}
(1 row)

只修改数组中的某个元素值的示例如下：

osdba=# update testtab09 set col1[2][1]=100  where id=1;
UPDATE 1
osdba=# select * from testtab09;
 id |                col1                 
----+-------------------------------------
  1 | {{10,11,12},{100,14,15},{16,17,18}}
(1 row)

注意，不能直接修改多维数组中某一维的值：

osdba=# update testtab09 set col1[3]=100  where id=1;
ERROR:  wrong number of array subscripts
osdba=# update testtab09 set col1[3]='{200,300}'  where id=1;
ERROR:  invalid input syntax for integer: "{200,300}"
LINE 1: update testtab09 set col1[3]='{200,300}'  where id=1;
                                     ^

5.15.5　数组的操作符

这里先讲解一下数组类型支持的操作符，具体见表5-37。

表5-37　数组类型支持的普通比较操作符

数组类型支持一些集合关系的操作符，见表5-38所示。

表5-38　数组类型的集合比较操作符

从表中可以看出，不同维度之间的数组也可以进行“包含”“重叠”等集合比较操作，这些操作与元素的顺序及维度基本无关，实际上可以认为，在做集合比较时，不管数组中的元素在哪一维，都可以把它们全部当成集合中的一个元素，而与数组的维度无关。

下面介绍最后一个操作符——连接操作符“||”，具体使用方法见表5-39。

表5-39　连接操作符“||”

5.15.6　数组的函数

与数组相关的函数见表5-40。

表5-40　数组函数

数组还有一个聚合函数array_agg，其使用方法如下。

首先建如下测试表：

osdba=# create table test03(id int, v int);
CREATE TABLE
osdba=# insert into test03 values(1,1);
INSERT 0 1
osdba=# insert into test03 values(1,2);
INSERT 0 1
osdba=# insert into test03 values(1,3);
INSERT 0 1
osdba=# insert into test03 values(2,20);
INSERT 0 1
osdba=# insert into test03 values(2,21);
INSERT 0 1
osdba=# insert into test03 values(2,22);
INSERT 0 1
osdba=# insert into test03 values(3,31);
INSERT 0 1
osdba=# insert into test03 values(3,32);
INSERT 0 1
osdba=# insert into test03 values(3,33);
INSERT 0 1
osdba=# select * from test03;
 id | v  
----+----
  1 |  1
  1 |  2
  1 |  3
  2 | 20
  2 | 21
  2 | 22
  3 | 31
  3 | 32
  3 | 33
(9 rows)

然后就可以使用array_agg聚合函数了：

osdba=# select id, array_agg(v) from test03 group by id;
 id | array_agg  
----+------------
  1 | {1,2,3}
  2 | {20,21,22}
  3 | {31,32,33}
(3 rows)

5.15 数组类型

5.15.1 数组类型的声明

5.15.2 如何输入数组值

5.15.3 访问数组

5.15.4 修改数组

5.15.5 数组的操作符