PostgreSQL数组函数使用技巧详解

PostgreSQL 18 在数组处理上提供了相当丰富的内置函数，这在实际开发中能极大提升数据操作的效率。下面就把这些常用函数整理了一遍，从创建到操作，从查询到转换，咱们一个一个来看。

创建数组

在 PostgreSQL 里创建数组，主要分两种情况：一是在表结构中定义数组字段，二是在查询过程中临时构造数组。

1. 在表中定义数组字段

建表的时候，在任意标准数据类型后面加个方括号 []，就能把这个字段声明为数组。

• 一维数组：直接用 数据类型[]
• 多维数组：用 数据类型[][] 写法，虽然 PostgreSQL 并不强制限制维度，但这样写语义上更清晰

-- 建一个包含一维和二维数组字段的表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT,
    tags TEXT[],           -- 文本类型的一维数组
    scores INTEGER[],      -- 整数类型的一维数组
    matrix INTEGER[][]     -- 整数类型的二维数组
);

PostgreSQL 也支持符合 SQL 标准的 ARRAY 关键字语法，比如 tags TEXT ARRAY 或 scores INTEGER ARRAY，怎么习惯怎么来。

有一点需要留意：PostgreSQL 目前不会强制限制数组的实际长度或维度，声明时指定的大小（比如 ``）更多是起个文档说明的作用。

2. 向数组字段插入数据

插入数组数据主要有两种写法：花括号 {} 或者 ARRAY 构造器。

-- 方式一：用 ARRAY 构造器（推荐，直观好读）
INSERT INTO users (name, tags, scores, matrix)
VALUES ('Alice', ARRAY['developer', 'gamer'], ARRAY[95, 88, 76], ARRAY[[1,2],[3,4]]);

-- 方式二：用花括号字面量（注意字符串通常要加双引号）
INSERT INTO users (name, tags, scores)
VALUES ('Bob', '{"teacher", "writer"}', '{75, 80, 85}');

3. 在查询中直接创建/构造数组

如果只是想在查询过程中临时生成一个数组，不需要存进表里，可以用下面几种方式：

用 ARRAY[] 构造器：

SELECT ARRAY[1,2,3];                -- 结果: {1,2,3}
SELECT ARRAY['a', 'b', 'c'];          -- 结果: {a,b,c}
SELECT ARRAY[[1,2],[3,4]];           -- 结果: {{1,2},{3,4}} (二维数组)

用 string_to_array 函数（把字符串按分隔符转成数组）：

SELECT string_to_array('apple,banana,orange', ','); -- 结果: {apple,banana,orange}

用 array_agg 聚合函数（把多行数据合并在一个数组里）：

-- 假设有张分数表，把某个用户的所有分数聚合成数组
SELECT array_agg(score) FROM user_scores WHERE user_id = 1;

补充小知识

数组下标从 1 开始：PostgreSQL 的数组索引默认从 1 开始，不是 0。

• 比如 SELECT tags[1] FROM users; 取的就是数组的第一个元素。

数组查询：可以用 ANY 或者包含操作符 @> 来判断数组中是否包含某个元素。

• 例如：SELECT * FROM users WHERE 'gamer' = ANY(tags);

数组基本信息与维度函数

array_length(anyarray, int)

返回数组在指定维度上的长度。

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

array_lower(anyarray, int)

返回数组在指定维度上的下界（最低索引）。

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

array_upper(anyarray, int)

返回数组在指定维度上的上界（最高索引）。

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

array_ndims(anyarray)

返回数组的维数，一维数组返回1，二维数组返回2，依此类推。

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

array_dims(anyarray)

返回数组维数的文本表示，包含下标范围。

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

数组增删与拼接函数

array_append(anyarray, anyelement)

向数组末尾添加一个元素。

SELECT array_append(ARRAY[1,2], 3);  -- 返回 {1,2,3}

array_prepend(anyelement, anyarray)

向数组开头添加一个元素。

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

array_cat(anyarray, anyarray)

把两个数组拼接成一个新数组。

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

array_remove(anyarray, anyelement)

从数组中删除所有等于指定值的元素。

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

数组搜索与定位函数

array_position(anyarray, anyelement [, integer])

返回指定元素在数组中第一次出现的位置下标（从1开始）。第三个可选参数可以指定从哪个下标开始搜索。

SELECT array_position(ARRAY['sun','mon','tue'], 'mon');  -- 返回 2

array_positions(anyarray, anyelement)

返回指定元素在数组中所有出现位置的下标数组。

SELECT array_positions(ARRAY[1,2,1,3], 1);  -- 返回 {1,3}

数组转换与聚合函数

unnest(anyarray)

把数组展开，返回一组行，常用于把数组数据转成多行记录。

SELECT unnest(ARRAY[1,2,3]);  -- 返回三行，分别为 1, 2, 3

array_agg(expression)

聚合函数，把多行查询结果收集合并成一个数组。

SELECT array_agg(id) FROM users;  -- 把 users 表的所有 id 聚合成一个数组

array_to_string(anyarray, text [, text])

把数组元素用指定分隔符拼接成一个字符串。第三个可选参数用来替代数组中的 NULL 值。

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

string_to_array(text, text [, text])

把字符串按指定分隔符拆分成一个数组。第三个可选参数指定哪些字符串被视为 NULL。

SELECT string_to_array('1,2,3', ',');  -- 返回 {1,2,3}

array_fill(anyelement, int[], [int[]])

用提供的值和维度（以及可选的下界）创建一个新数组。

SELECT array_fill(7, ARRAY[3]);  -- 返回 {7,7,7}

array_sort(anyarray)

对数组的第一维元素进行升序排序。

SELECT array_sort(ARRAY[9,1,5,4,3,1]); -- 结果: {1,1,3,4,5,9}

array_reverse(anyarray)

把数组中的元素顺序完全反转。

SELECT array_reverse(ARRAY[1,2,3,4,5]); -- 结果: {5,4,3,2,1}

数组操作符

除了函数，PostgreSQL 的数组操作符同样是处理数组数据的核心工具。

数组比较操作符

这些操作符对两个数组进行逐个元素的比较。

要注意的是，原生数组比较是严格匹配，不但要求元素完全一致，顺序和维度也必须完全相同。

• 顺序敏感：元素相同但顺序不同，会被视为不相等。
• 类型与维度敏感：底层类型或维度不同也会影响比较结果。

• =：判断两个数组是否相等。
• <>：判断两个数组是否不相等。
• <：判断左侧数组是否小于右侧数组。
• >：判断左侧数组是否大于右侧数组。
• <=：判断左侧数组是否小于或等于右侧数组。
• >=：判断左侧数组是否大于或等于右侧数组。

提示：数组的排序比较（<、> 等）基于 B-tree 比较函数逐个元素进行，直到遇到第一个不同的元素来决定大小。

由于原生 = 对顺序敏感，如果顺序不重要，可以先排序再比较。

数组拼接操作符 (||)

|| 操作符非常灵活，既可用于数组之间的拼接，也能把单个元素和数组拼接。

数组与数组拼接：把两个数组合并。

SELECT ARRAY[1,2,3] || ARRAY[4,5,6];  -- 返回 {1,2,3,4,5,6}

元素与数组拼接（前置）：把元素添加到数组开头。

SELECT 3 || ARRAY[4,5,6];  -- 返回 {3,4,5,6}

数组与元素拼接（后置）：把元素添加到数组末尾。

SELECT ARRAY[4,5,6] || 7;  -- 返回 {4,5,6,7}

数组包含与重叠操作符

这类操作符常用于复杂的条件过滤，判断数组之间的包含关系或是否有共同元素。

• @>：包含。判断左侧数组是否包含右侧数组（右侧数组的所有元素都在左侧中间出现过）。
• <@：被包含于。判断左侧数组是否被右侧数组包含（左侧数组的所有元素都在右侧中间出现过）。

SELECT ARRAY[1,2] <@ ARRAY[1,2,3];  -- 返回 true

&&：重叠。判断两个数组是否有共同元素，只要有一个相同即为 true。

SELECT ARRAY[1,2,3] && ARRAY[3,4,5];  -- 返回 true

提示：包含操作符（@> 和 <@）中，重复的元素值不做特殊处理。比如 ARRAY[1,2,2] 和 ARRAY[1,2] 会被认为互相包含。

把这些操作符和前一阶段的数组函数配合起来，基本上 PostgreSQL 里各种奇奇怪怪的数组业务需求就都能应付了。