PostgreSQL数组函数的应用

作者：袖梨 2026-06-04

在 PostgreSQL 18 中，数组类型提供了非常丰富的内置函数，可以极大地提升对数组数据的处理效率。以下为整理了 PostgreSQL 操作数组的常用函数全集及详细讲解：

PostgreSQL数组函数的使用

? 创建数组

在 PostgreSQL 中创建数组，通常分为在表中定义数组字段和在查询中直接构造数组两种情况。

以下是详细的操作方法：

1. 在表中定义数组字段

在创建表时，可以在任何标准的数据类型（如 TEXT、INTEGER 等）后面加上方括号 [] 来声明该字段为数组。

  • 一维数组：使用 数据类型[]
  • 多维数组：使用 数据类型[][]（实际上 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。

2. 向数组字段插入数据

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

-- 方式一：使用 ARRAY 构造器（推荐，更直观）INSERT INTO users (name, tags, scores, matrix)VALUES ('Alice', ARRAY['developer', 'gamer'], ARRAY, ARRAY[,]);-- 方式二：使用花括号字面量（注意内部字符串通常需要加双引号）INSERT INTO users (name, tags, scores)VALUES ('Bob', '{"teacher", "writer"}', '{75, 80, 85}');

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

如果不需要将数组存入表中，只是想在 SQL 查询过程中临时创建一个数组，可以使用以下几种方式：

使用 ARRAY[] 构造器：sql

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

使用 string_to_array 函数（将字符串按分隔符转为数组）：sql

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

使用 array_agg 聚合函数（将多行数据合并为一个数组）：sql

-- 假设有一个包含分数的表，将分数聚合成数组SELECT array_agg(score) FROM user_scores WHERE user_id = 1;

? 补充小知识

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

  • 例如 SELECT tags FROM users; 会取出数组的第一个元素。

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

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

? 数组基本信息与维度函数

array_length(anyarray, int)

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

SELECT array_length(ARRAY, 1);  -- 返回

array_lower(anyarray, int)

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

SELECT array_lower(ARRAY, 1);  -- 返回 1

array_upper(anyarray, int)

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

SELECT array_upper(ARRAY, 1);  -- 返回

array_ndims(anyarray)

返回数组的维数（例如一维数组返回1，二维数组返回2）。

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

array_dims(anyarray)

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

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

➕ 数组增删与拼接函数

array_append(anyarray, anyelement)

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

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

array_prepend(anyelement, anyarray)

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

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

array_cat(anyarray, anyarray)

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

SELECT array_cat(ARRAY1, ARRAY2);  -- 返回 {1,2,3,4,5,6}

array_remove(anyarray, anyelement)

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

-- 例如从数组中删除元素 3SELECT array_remove(ARRAY, 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);  -- 返回 {1,4}

? 数组转换与聚合函数

unnest(anyarray)

将数组展开，返回一组行（Set of rows），常用于将数组数据转换成多行记录。

SELECT unnest(ARRAY);  -- 返回三行，分别为 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'

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);  -- 返回 {7,7,7}

array_sort(array1 )

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

-- 将数组元素按升序排列SELECT array_sort(ARRAY1);-- 结果: {1,1,3,4,5,9}

array__reverse(anyarray)

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

-- 将数组元素倒序排列SELECT array_reverse(ARRAY);-- 结果: {5,4,3,2,1}

☝ (二)，数组操作符

在 PostgreSQL 中，除了丰富的数组函数外，数组操作符也是处理数组数据的核心工具。

⚖️ 数组比较操作符

这些操作符用于对两个数组进行比较，它们会对数组内容进行逐个元素的对比。

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

顺序敏感：如果两个数组包含的元素相同，但顺序不同，它们会被视为不相等。

类型与维度敏感：即使通过类型转换让元素值看起来一样，如果底层类型或维度不同，也可能影响比较结果（不过在标准的同类型比较中，= 会进行合理的类型匹配）。

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

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

由于原生的 = 操作符对顺序敏感，可以通过先排序再比较的方式来实现。

? 数组拼接操作符 (||)

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

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

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

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

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

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

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

? 数组包含与重叠操作符

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

  • @> ：包含。判断左侧数组是否包含右侧数组（即右侧数组的所有元素都在左侧数组中出现过）。
  • <@ ：被包含于。判断左侧数组是否被右侧数组包含（即左侧数组的所有元素都在右侧数组中出现过）。
SELECT ARRAY1 <@ ARRAY2;  -- 返回 true

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

SELECT ARRAY1 && ARRAY2;  -- 返回 true

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

掌握这些操作符，配合上一轮提到的数组函数，就能在 PostgreSQL 中游刃有余地处理各种复杂的数组业务逻辑了

相关文章

精彩推荐