多维数组

多维数组（Multidimensional Array）是 PostgreSQL 数组里最容易"看起来能干很多事、实际却很少用"的功能。本节讲清它的语法与语义、矩形约束、切片行为，以及为什么生产环境往往选择 JSONB 或关系表来替代它。

前置知识

阅读本节前，建议先了解：数组函数

基础概念

多维数组（Multidimensional Array）

多维数组是把数组当成"矩阵"来用：第一维是行，第二维是列，还可以继续叠第三维。

text

ARRAY[[1, 2, 3],
      [4, 5, 6]]         -- 2 × 3 的矩阵

维度（Dimension）与下标（Subscript）

PostgreSQL 的数组不是"数组的数组"，而是一个真正的多维结构。这一点非常关键，直接决定了它的行为：

特性	PostgreSQL 多维数组	嵌套数组（如 JSON / 编程语言）
元素访问	`arr[i][j]`	`arr[i][j]`
子数组长度	必须严格一致（矩形）	可以参差不齐
子数组能否单独取出	部分支持，但语义特殊	可以
维度数是否可变	固定，`array_ndims` 决定	灵活

矩形约束（Rectangular Constraint）

多维数组的每一级子数组长度必须相同，否则直接报错。

sql

-- 合法：2 × 3
SELECT ARRAY[[1,2,3],[4,5,6]] AS ok;

-- 非法：子数组长度不一致
SELECT ARRAY[[1,2,3],[4,5]] AS bad;
-- ERROR:  multidimensional arrays must have array expressions with matching dimensions

下界（Lower Bound）

每一维都有自己的下界。默认从 1 开始，切片会保留原下界。

sql

SELECT array_lower(ARRAY[[1,2],[3,4]], 1) AS lb1,   -- 1
       array_lower(ARRAY[[1,2],[3,4]], 2) AS lb2;   -- 1

术语提示

多维数组（Multidimensional Array）、维度（Dimension）、下标（Subscript）、矩形约束（Rectangular Constraint）、下界（Lower Bound）、展开（Unnest）。本节频繁使用这些术语。

语法与用法

多维数组的声明与构造语法：

text

-- 类型声明
integer[][]                  -- 不指定维度数，可为任意维度
integer[3][3]                -- 维度数 2（长度仍不限制）

-- 字面量
'{{1,2,3},{4,5,6}}'

-- 构造器
ARRAY[[1,2,3],[4,5,6]]
ARRAY[[1,2],[3,4]] || ARRAY[[5,6]]     -- 沿第一维拼接

-- 访问
arr[1][2]                    -- 第 1 行第 2 列（不能用 arr[1,2]）
arr[2]                       -- 第 2 行的全部元素，结果仍是数组
arr[1:2][1:1]                -- 二维切片

建表与插入：

sql

DROP TABLE IF EXISTS t_matrix;

CREATE TABLE t_matrix (
  id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT,
  m    INTEGER[][]      -- 任意维度整数矩阵
);

INSERT INTO t_matrix (name, m) VALUES
  ('2x3 矩阵', '{{1,2,3},{4,5,6}}'),
  ('3x3 矩阵', '{{1,0,0},{0,1,0},{0,0,1}}'),
  ('1 维数组', '{7,8,9}');

详细说明

维度数与形状的获取

sql

SELECT id, name,
       array_ndims(m)  AS ndims,
       array_dims(m)   AS dims,
       cardinality(m)  AS total
FROM t_matrix
ORDER BY id;

-- 结果：
--  id |   name   | ndims |    dims     | total
-- ----+----------+-------+-------------+-------
--   1 | 2x3 矩阵  |     2 | [1:2][1:3]  |     6
--   2 | 3x3 矩阵  |     2 | [1:3][1:3]  |     9
--   3 | 1 维数组  |     1 | [1:3]       |     3

array_length 需要分别查询每一维：

sql

SELECT array_length(m, 1) AS rows, array_length(m, 2) AS cols
FROM t_matrix
WHERE id = 1;

-- 结果：
--  rows | cols
-- ------+------
--     2 |    3

元素访问与整行访问

用连续的两个方括号访问元素，不能写成 arr[1,2]。

sql

SELECT m[1][2] AS elem_1_2,   -- 2
       m[2][3] AS elem_2_3    -- 6
FROM t_matrix
WHERE id = 1;

只写第一维下标会取出"整行"：

sql

SELECT m[2] AS row2 FROM t_matrix WHERE id = 1;    -- {4,5,6}

注意 m[2] 的结果仍然是一个数组，只是维度降为 1。这一点与"取切片"的效果一致。

常见的下标错误：

sql

SELECT m[1,2] FROM t_matrix WHERE id = 1;
-- ERROR:  syntax error at or near ","

切片访问

多维切片用多组 [lower:upper] 表示，语法上必须连续书写。

sql

-- 取第 1~2 行、第 2~3 列
SELECT m[1:2][2:3] AS sub
FROM t_matrix WHERE id = 1;

-- 结果：
--     sub
-- -----------
--  {{2,3},{5,6}}

只切第一维：

sql

SELECT m[2:2] AS only_row2 FROM t_matrix WHERE id = 1;   -- {{4,5,6}}

注意

切片结果的下界会被保留。m[2:2] 的结果 array_lower(..., 1) 是 2 而不是 1，这会让后续的 arr[1] 访问失败。需要重置下标时用 array_agg / unnest 重建，或先取 m[1:1] 之类的规范化写法。

`unnest` 展开多维数组

unnest 会把多维数组完全打平成一维的行序列，维度信息丢失。

sql

SELECT unnest(m) AS elem
FROM t_matrix WHERE id = 1;

-- 结果：
--  elem
-- ------
--     1
--     2
--     3
--     4
--     5
--     6

如果需要保留行号信息，需要先切行再展开：

sql

-- 按行展开，保留行号
SELECT r AS row_no, unnest(m[r:r]) AS elem
FROM t_matrix, generate_series(1, array_length(m, 1)) AS r
WHERE id = 1
ORDER BY row_no;

-- 结果：
--  row_no | elem
-- --------+------
--       1 |    1
--       1 |    2
--       1 |    3
--       2 |    4
--       2 |    5
--       2 |    6

逐元素运算

PostgreSQL 不提供多维数组的逐元素运算（如矩阵相加）。所有"逐元素"需求都要先 unnest，处理后重组。

sql

-- 矩阵加常数：必须展开 → 处理 → 重组
SELECT array_agg(ARRAY(SELECT elem + 10 FROM unnest(m[r:r]) AS elem ORDER BY elem))
FROM t_matrix, generate_series(1, array_length(m,1)) AS r
WHERE id = 1;

这段 SQL 的复杂度已经暴露了多维数组的短板：做任何实质计算都要绕一大圈。

沿第一维拼接

|| 对多维数组只沿第一维拼接，且要求其余维度一致。

sql

SELECT ARRAY[[1,2],[3,4]] || ARRAY[[5,6]] AS appended;
-- 结果：{{1,2},{3,4},{5,6}}

-- 维度不匹配会报错
SELECT ARRAY[[1,2],[3,4]] || ARRAY[[5,6,7]];
-- ERROR:  cannot concatenate incompatible arrays

为什么实际用途有限

多维数组在 PostgreSQL 中少见，原因可以归结为四点：

必须矩形：真实数据（如不同用户的不同数量观测值）几乎从不满足矩形约束。
没有矩阵运算：没有矩阵乘法、逐元素加减、转置等函数。
索引能力弱：GIN 的 array_ops 只能给整个展开的元素建索引，无法按"第 2 列"建索引。
更新代价高：修改任意一个元素都要重写整行（见数组索引与性能）。

替代建模方案

方案一：JSONB——适合"结构不固定、需要按路径查询"的场景。

sql

DROP TABLE IF EXISTS t_jsonb;

CREATE TABLE t_jsonb (
  id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  data JSONB NOT NULL
);

-- 不等长的嵌套结构可以正常工作
INSERT INTO t_jsonb (data) VALUES
  ('{"rows": [[1,2,3],[4,5]]}'),
  ('{"rows": [[1],[2,3],[4,5,6]]}');

-- 按路径查询
SELECT id, data -> 'rows' -> 1 AS second_row FROM t_jsonb ORDER BY id;

-- 结果：
--  id | second_row
-- ----+------------
--   1 | [4, 5]
--   2 | [2, 3]

方案二：关系表——适合"需要按维度筛选、统计、建索引"的场景。

sql

DROP TABLE IF EXISTS matrix_cells;

CREATE TABLE matrix_cells (
  matrix_id BIGINT NOT NULL,
  row_no    INT    NOT NULL,
  col_no    INT    NOT NULL,
  val       NUMERIC,
  PRIMARY KEY (matrix_id, row_no, col_no)
);

INSERT INTO matrix_cells VALUES
  (1, 1, 1, 1), (1, 1, 2, 2), (1, 1, 3, 3),
  (1, 2, 1, 4), (1, 2, 2, 5), (1, 2, 3, 6);

-- 按列聚合（多维数组做不到这么快）
SELECT col_no, sum(val) AS col_sum
FROM matrix_cells
WHERE matrix_id = 1
GROUP BY col_no
ORDER BY col_no;

-- 结果：
--  col_no | col_sum
-- --------+---------
--       1 |       5
--       2 |       7
--       3 |       9

三种方案对比表：

维度	多维数组	JSONB	关系表
矩形约束	强制	无	无
按维度索引	否	部分（GIN 路径）	是（复合 B-tree）
逐元素运算	需绕路	需绕路	原生 SQL
存储开销	低	中	高
适用场景	小规模、天然矩阵	结构灵活的嵌套数据	需要分析与索引的矩阵
更新代价	整行重写	整行重写	单行更新

实战示例

下面用一个"传感器读数矩阵"场景，对比三种建模方式。

第 1 步：多维数组建表

sql

DROP TABLE IF EXISTS sensor_matrix;

CREATE TABLE sensor_matrix (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  device_id   TEXT NOT NULL,
  reading_date DATE NOT NULL,
  readings    NUMERIC[][] NOT NULL,   -- 行=小时段，列=指标（温度/湿度/气压）
  UNIQUE (device_id, reading_date)
);

INSERT INTO sensor_matrix (device_id, reading_date, readings) VALUES
  ('sensor-01', '2026-09-01', '{{22.1,55,1013},{23.4,52,1012}}'),
  ('sensor-01', '2026-09-02', '{{21.8,58,1014},{24.0,50,1011}}');

第 2 步：查询矩阵形状

sql

SELECT device_id, reading_date,
       array_length(readings, 1) AS slots,
       array_length(readings, 2) AS metrics
FROM sensor_matrix
ORDER BY reading_date;

-- 结果：
--  device_id  | reading_date | slots | metrics
-- ------------+--------------+-------+---------
--  sensor-01  | 2026-09-01   |     2 |       3
--  sensor-01  | 2026-09-02   |     2 |       3

第 3 步：取出某个时段的读数

sql

SELECT device_id, readings[1] AS first_slot, readings[2] AS second_slot
FROM sensor_matrix
WHERE reading_date = '2026-09-01';

-- 结果：
--  device_id  |   first_slot    |  second_slot
-- ------------+-----------------+-----------------
--  sensor-01  | {22.1,55,1013}  | {23.4,52,1012}

第 4 步：用关系表重写同一场景

sql

DROP TABLE IF EXISTS sensor_readings;

CREATE TABLE sensor_readings (
  device_id    TEXT    NOT NULL,
  reading_date DATE    NOT NULL,
  slot_no      INT     NOT NULL,
  metric       TEXT    NOT NULL,   -- temperature / humidity / pressure
  value        NUMERIC NOT NULL,
  PRIMARY KEY (device_id, reading_date, slot_no, metric)
);

INSERT INTO sensor_readings VALUES
  ('sensor-01','2026-09-01',1,'temperature',22.1),
  ('sensor-01','2026-09-01',1,'humidity',   55),
  ('sensor-01','2026-09-01',1,'pressure',   1013),
  ('sensor-01','2026-09-01',2,'temperature',23.4),
  ('sensor-01','2026-09-01',2,'humidity',   52),
  ('sensor-01','2026-09-01',2,'pressure',   1012);

第 5 步：关系表下的灵活分析

sql

-- 每个指标的平均值（多维数组很难写）
SELECT metric, round(avg(value), 2) AS avg_value
FROM sensor_readings
GROUP BY metric
ORDER BY metric;

-- 结果：
--    metric    | avg_value
-- -------------+-----------
--  humidity    |     53.50
--  pressure    |   1012.50
--  temperature |     22.75

-- 某设备所有日期的温度序列
SELECT reading_date, array_agg(value ORDER BY slot_no) AS temps
FROM sensor_readings
WHERE device_id = 'sensor-01' AND metric = 'temperature'
GROUP BY reading_date
ORDER BY reading_date;

-- 结果：
--  reading_date |   temps
-- --------------+-----------
--  2026-09-01   | {22.1,23.4}

第 6 步：清理

sql

DROP TABLE sensor_matrix;
DROP TABLE sensor_readings;

注意事项

矩形约束是硬性的：子数组长度不一致会直接报错，无法插入。这与 JSON 数组完全不同。
下标必须写成 m[i][j]：逗号写法 m[i,j] 是语法错误。
m[i] 返回的仍是一维数组：不是标量，注意类型。
unnest 会丢失维度信息：打平后无法还原行列关系，需要额外手段（如 generate_series 配合切片）。
没有矩阵运算函数：加法、乘法、转置全部需要手工实现。
切片结果下界不一定为 1：会影响后续下标访问，务必用 array_lower 确认。
|| 只在第一维拼接：且要求其余维度完全一致。
GIN 索引对多维数组价值有限：它索引的是打平后的元素集合，无法表达"第 2 列"这类语义。
类型声明 integer[][] 不限制维度数：它只是"数组的数组"的类型速记，实际维度由数据决定。
不宜作为报表数据的载体：聚合、排序、时空查询都会很别扭。

最佳实践

优先问一句"是否真的是矩阵"：长度必须一致、需要行列分析，才考虑多维数组。
首选关系表：需要按维度统计、建索引、做约束时，关系表几乎总是更好的选择。
结构不固定时用 JSONB：允许参差不齐、需要路径查询，选 JSONB。
只在内存计算中使用多维数组：如一次性生成报表矩阵，用 array_agg + ARRAY[] 构造即可，不必落库。
需要保留行列位置时用 generate_series：这是展开二维数组的标准技巧。
给多维数组列加 CHECK 约束校验形状：例如 CHECK (array_ndims(m) = 2)。
不要指望 GIN 索引加速"按列"查询：那属于关系建模的领域。
避免在应用层与数据库层来回转换：应用侧的矩阵结构与 PG 多维数组语义不同，转换成本往往被低估。

继续学习：数组索引与性能

参考链接

PostgreSQL 官方文档：Arrays
PostgreSQL 官方文档：Array Functions and Operators
PostgreSQL 官方文档：JSON Types
PostgreSQL 官方文档：Subquery Expressions

多维数组 ​

基础概念 ​

多维数组（Multidimensional Array） ​

维度（Dimension）与下标（Subscript） ​

矩形约束（Rectangular Constraint） ​

下界（Lower Bound） ​

语法与用法 ​

详细说明 ​

维度数与形状的获取 ​

元素访问与整行访问 ​

切片访问 ​

unnest 展开多维数组 ​

逐元素运算 ​

沿第一维拼接 ​

为什么实际用途有限 ​

替代建模方案 ​

实战示例 ​

第 1 步：多维数组建表 ​

第 2 步：查询矩阵形状 ​

第 3 步：取出某个时段的读数 ​

第 4 步：用关系表重写同一场景 ​

第 5 步：关系表下的灵活分析 ​

第 6 步：清理 ​