【PostgreSQL】PostgreSQL数组类型常见操作

---

PostgreSQL允许一个表中的列定义为变长多维数组。可以创建任何内建或用户定义的基类、枚举类型、组合类型或者域的数组。

一、数组类型的定义

1.1、[]方式定义

pay_by_quarter  integer[],
schedule        text[][]
squares   integer[3][3]

1.2、使用关键词ARRAY定义

 pay_by_quarter  integer ARRAY[4],
pay_by_quarter  integer ARRAY,

---

二、数组操作符

操作符	描述	例子	结果
=	等于	ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3]	t
<>	不等于	ARRAY[1,2,3] <> ARRAY[1,2,4]	t
<	小于	ARRAY[1,2,3] < ARRAY[1,2,4]	t
>	大于	ARRAY[1,4,3] > ARRAY[1,2,4]	t
<=	小于等于	ARRAY[1,2,3] <= ARRAY[1,2,3]	t
>=	大于等于	ARRAY[1,4,3] >= ARRAY[1,4,3]	t
@>	包含	ARRAY[1,4,3] @> ARRAY[3,1,3]	t
<@	被包含	ARRAY[2,2,7] <@ ARRAY[1,7,4,2,6]	t
&&	重叠（具有公共元素）	ARRAY[1,4,3] && ARRAY[2,1]	t

数组和数组串接：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}

说明：

数组排序操作符（<、>=等）对数组内容进行逐个元素的比较，使用默认的元素数据类型的B-tree比较函数，并根据第一个差值进行排序。 多维数组的元素按照行序进行访问（最后的下标变化最快）。如果两个数组的内容相同但维数不等，那么维度信息中的第一个不同将决定排序顺序（这是对PostgreSQL 8.2 之前版本的修改： 老版本认为内容相同的两个数组相等，即使它们的维数或下标范围并不相同）。

数组包含操作符（<@和@>）认为，如果一个数组的每一个元素出现在另一个数组中，那么这个数组就被包含在另一个数组中。重复数组不做特殊处理，因此ARRAY[1]和ARRAY[1,1]分别被认为包含了另一个数组。

---

三、常用数组函数

在PostgreSQL中，数组是一种非常有用的数据类型，可以用来存储一组相同类型的数据。PostgreSQL提供了一些常用的数组函数，以下是一些常用的数组函数及其使用示例：

3.1、array_length(array, dimension)：返回数组的长度。

SELECT array_length(ARRAY[1,2,3,4,5], 1);  -- 返回 5

3.2、array_lower(array, dimension)：返回数组的最低索引。

SELECT array_lower(ARRAY[1,2,3,4,5], 1);  -- 返回 1

3.3、array_upper(array, dimension)：返回数组的最高索引。

SELECT array_upper(ARRAY[1,2,3,4,5], 1);  -- 返回 5

3.4、array_cat(array1, array2)：连接两个数组。

SELECT array_cat(ARRAY[1,2,3], ARRAY[4,5,6]);  -- 返回 [1,2,3,4,5,6]

3.5、array_ndims(array)：返回数组的维数。

SELECT array_ndims(ARRAY[[1,2], [3,4]]);  -- 返回 2

3.6、unnest(array)：将数组展开成一系列的行。

SELECT unnest(ARRAY[1,2,3,4,5]);  -- 返回多行，每行一个值

3.7、array_agg(expression)：聚合函数，将多个行的值收集到一个数组中。

SELECT array_agg(column_name) FROM table_name;  -- 返回一个数组，包含指定列的所有行

3.8、array_to_string(array, delimiter)：将数组转换成字符串。

SELECT array_to_string(ARRAY[1,2,3,4,5], ', ');  -- 返回 '1, 2, 3, 4, 5'

3.9、string_to_array(string, delimiter)：将字符串转换成数组。

SELECT string_to_array('1,2,3,4,5', ', ');  -- 返回 ARRAY[1,2,3,4,5]

3.10、array_prepend(element, array)：将元素添加到数组的开始。

SELECT array_prepend(1, ARRAY[2,3,4,5]);  -- 返回 [1,2,3,4,5]

3.11、array_append(element, array)：将元素添加到数组的末尾。

SELECT array_append(1, ARRAY[2,3,4,5]);  -- 返回 [2,3,4,5,1]

3.12、array_position(array, element, [start])：返回元素在数组中的位置。

SELECT array_position(ARRAY[1,2,3,4,5], 3);  -- 返回 3

3.13、array_remove(array, element)：从数组中移除元素。

SELECT array_remove(ARRAY[1,2,3,2,1], 2);  -- 返回 [1,3,1]

这些函数可以帮助你在使用PostgreSQL时高效地处理数组数据。

---

四、数组类型数据的增删改查

4.1、创建测试表

-- 创建测试表
CREATE TABLE sal_emp (
    name            text,         -- 雇员姓名
    pay_by_quarter  integer[],    -- 雇员季度工资
    schedule        text[][]      -- 雇员每周日程表
);

4.2、插入数据

4.2.1、方式1：'{}'将数据转为数组

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

4.2.2、方式2：使用ARRAY构造器

INSERT INTO sal_emp
    VALUES ('Bill',
    ARRAY[10000, 10000, 10000, 10000],
    ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);

INSERT INTO sal_emp
    VALUES ('Carol',
    ARRAY[20000, 25000, 25000, 25000],
    ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);

4.2.3、关于null

要设置一个数组常量的一个元素为NULL，在该元素值处写NULL（任何NULL的大写或小写变体都有效）。如果你需要一个真正的字符串值“NULL”，你必须在它两边放上双引号。

多维数组的每一维都必须有相匹配的长度。不匹配会造成错误，例如：

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"meeting"}}');
ERROR:  multidimensional arrays must have array expressions with matching dimensions

最后插入的数据

postgres=# select * from sal_emp;
 name  |      pay_by_quarter       |                 schedule
-------+---------------------------+-------------------------------------------
 Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
 Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
(2 rows)

---

4.3、查询数据

4.3.1、使用数组下标

数组下标写在方括号内。默认情况下，PostgreSQL为数组使用了一种从1开始的编号习惯，即一个具有n个元素的数组从array[1]开始，结束于array[n]。

-- 检索在第二季度工资发生变化的雇员的名字
SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
 name
-------
 Carol
(1 row)

-- 检索所有员工第三季度的工资
SELECT pay_by_quarter[3] FROM sal_emp;
 pay_by_quarter
----------------
          10000
          25000
(2 rows)

4.3.2、数组切片

通过在一个或多个数组维度上指定 下界:上界 来定义

-- 检索Bill在本周头两天日程中的第一项
SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';
        schedule
------------------------
 {{meeting},{training}}
(1 row)

如果任何维度被写成一个切片，即包含一个冒号，那么所有的维度都被看成是切片对待。其中任何只有一个数字（无冒号）的维度被视作是从1到指定的数字。

-- 下面例子中的[2]被认为是[1:2]： 
SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';
                 schedule
-------------------------------------------
 {{meeting,lunch},{training,presentation}}
(1 row)

为了避免和非切片情况搞混，最好在所有的维度上都使用切片语法，例如[1:2][1:1]而不是[2][1:1]。

省略一个切片说明符的*lower-bound*或者 upper-bound（亦可两者都省略），缺失的 边界会被数组下标的上下限所替代。

SELECT schedule[:2][2:] FROM sal_emp WHERE name = 'Bill';
        schedule
------------------------
 {{lunch},{presentation}}
(1 row)

SELECT schedule[:][1:1] FROM sal_emp WHERE name = 'Bill';
        schedule
------------------------
 {{meeting},{training}}
(1 row)

4.3.3、返回null值的场景

• 数组本身为null
• 任何一个下标表达式为空
• 下标超过了数组边界（不会抛出错误）

-- 数组本身为null
with tmp_array as
(
select ARRAY[['breakfast', 'consulting'], ['meeting','']] as name)
select name[2][2] from tmp_array;

-- 下标为空
with tmp_array as
(
select ARRAY[['breakfast', 'consulting'], ['meeting','']] as name)
select name[2] from tmp_array;

-- 下标越界
with tmp_array as
(
select ARRAY[['breakfast', 'consulting'], ['meeting','']] as name)
select name[2][3] from tmp_array;

4.3.4、查询数据相关信息

4.3.4.1、获取数组维度

SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';
 array_dims
------------
 [1:2][1:2]
(1 row)

SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';
 array_upper
-------------
           2
(1 row)

4.3.4.2、将返回一个指定数组维度的长度

SELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol';
 array_length
--------------
            2
(1 row)

4.3.4.3、返回一个数组中在所有维度上的元素总数

SELECT cardinality(schedule) FROM sal_emp WHERE name = 'Carol';
 cardinality
-------------
           4
(1 row)

-- 等价于
select count(*) from (select unnest(schedule) FROM sal_emp WHERE name = 'Carol') t;

---

4.4、修改数组

4.4.1、数组值整个被替换

UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
    WHERE name = 'Carol';

-- OR

UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
    WHERE name = 'Carol';

4.4.2、使用下标指定元素更新

UPDATE sal_emp SET pay_by_quarter[4] = 15000
    WHERE name = 'Bill';

4.4.3、在一个切片上被更新

UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
    WHERE name = 'Carol';

---

4.5、数组值扩充

4.5.1、update不存在的下标（只允许使用在一维数组上）

drop table if exists test_t;
create table test_t(id int[]);
insert into test_t values ('{1,2,3,4}');

postgres=# select * from test_t;
    id
-----------
 {1,2,3,4}
(1 row)
postgres=# update test_t set id[6] = 6;
UPDATE 1
postgres=# select * from test_t;
        id
------------------
 {1,2,3,4,NULL,6}
(1 row)

带下标的赋值方式允许创建下标不是从1开始的数组。

postgres=# select * from test_t;
        id
------------------
 {1,2,3,4,NULL,6}
(1 row)
postgres=# update test_t set id[-2] = -2;
UPDATE 1
postgres=# select * from test_t;
              id
-------------------------------
 [-2:4]={-2,NULL,NULL,1,2,3,4}
(1 row)

4.5.2、通过串接操作符||构建

SELECT ARRAY[1,2] || ARRAY[3,4];
 ?column?
-----------
 {1,2,3,4}
(1 row)

SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];
      ?column?
---------------------
 {{5,6},{1,2},{3,4}}
(1 row)

参考资料

官方文档：
https://www.postgresql.org/docs/current/arrays.html
https://www.postgresql.org/docs/current/functions-array.html