arrays

PostgreSQL 作为功能丰富的关系型数据库，提供了强大的数组支持，允许将列定义为可变长度的多维数组。本文将深入探讨 PostgreSQL 数组的各个方面，包括类型声明、值输入、访问方式、修改操作和搜索技巧。

数组概述

PostgreSQL 支持对任何内置类型、用户定义类型、枚举类型、复合类型、范围类型或域创建数组。这种灵活性使得数组在处理相关数据集合时成为一个强大的工具。

INFO

数组在某些场景下很有用，但过度使用可能表明数据库设计不佳。在考虑使用数组之前，请评估是否使用规范化的表结构更合适。

8.15.1 数组类型的声明

基本语法

数组类型通过在基础类型名称后添加方括号 [] 来声明：

sql

-- 创建包含数组列的表
CREATE TABLE sal_emp (
    name            text,           -- 员工姓名
    pay_by_quarter  integer[],      -- 季度薪水（一维数组）
    schedule        text[][]        -- 工作安排（二维数组）
);

多维数组声明

可以声明多维数组，并可选择指定维度大小：

sql

-- 声明具有指定大小的数组
CREATE TABLE tictactoe (
    squares   integer[3][3]  -- 3x3 的井字游戏棋盘
);

PostgreSQL 当前实现会忽略数组大小限制，所有声明的大小仅作为文档说明，不影响运行时行为。

SQL 标准语法

PostgreSQL 也支持符合 SQL 标准的 ARRAY 关键字语法：

sql

CREATE TABLE employees (
    name text,
    pay_by_quarter integer ARRAY[4],  -- 指定大小
    skills text ARRAY                 -- 不指定大小
);

数组特性说明

8.15.2 数组值输入

文字常量语法

使用大括号和逗号分隔的语法输入数组值：

sql

-- 基本语法格式
'{ val1, val2, val3, ... }'

-- 插入数据示例
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"}}'
);

查看插入结果

sql

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}}

ARRAY 构造函数语法

使用 ARRAY 构造函数提供更清晰的语法：

sql

INSERT INTO sal_emp VALUES (
    'David',
    ARRAY[15000, 15000, 16000, 16000],
    ARRAY[['coding', 'review'], ['meeting', 'testing']]
);

ARRAY 构造函数语法中，元素值使用标准 SQL 常量语法（字符串用单引号），而数组文字语法中字符串元素用双引号。

多维数组要求

多维数组的每个维度必须具有匹配的范围：

sql

-- 错误示例：维度不匹配
INSERT INTO sal_emp VALUES (
    'Error',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"meeting"}}'  -- 第二个子数组缺少元素
);
-- ERROR: malformed array literal

NULL 值处理

sql

-- 在数组中使用 NULL 值
INSERT INTO sal_emp VALUES (
    'Alice',
    '{15000, NULL, 17000, 18000}',
    '{{"planning", "design"}, {NULL, "deployment"}}'
);

-- 如果需要字面值 "NULL"，必须用双引号括起来
INSERT INTO sal_emp VALUES (
    'Bob',
    '{16000, 16000, 16000, 16000}',
    '{{"meeting", "lunch"}, {"training", "\"NULL\""}}'
);

8.15.3 访问数组

单元素访问

PostgreSQL 使用基于 1 的索引系统访问数组元素：

sql

-- 查找第二季度工资发生变化的员工
SELECT name FROM sal_emp
WHERE pay_by_quarter[1] <> pay_by_quarter[2];

输出：

 name
-------
 Carol

sql

-- 获取所有员工的第三季度工资
SELECT name, pay_by_quarter[3] AS third_quarter
FROM sal_emp;

输出：

 name  | third_quarter
-------+---------------
 Bill  |         10000
 Carol |         25000
 David |         16000

数组切片操作

使用 下限:上限 语法进行切片操作：

sql

-- 获取 Bill 前两天的工作安排第一项
SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';

输出：

        schedule
------------------------
 {{meeting},{training}}

切片边界处理

sql

-- 省略下限（默认为1）
SELECT schedule[:2][2:] FROM sal_emp WHERE name = 'Bill';
-- 输出: {{lunch},{presentation}}

-- 省略上限（默认为数组上限）
SELECT schedule[:][1:1] FROM sal_emp WHERE name = 'Bill';
-- 输出: {{meeting},{training}}

切片操作中，如果指定的范围超出数组边界，PostgreSQL 会自动调整到有效范围，而不会产生错误。

数组维度信息函数

sql

-- 获取数组维度信息
SELECT
    name,
    array_dims(schedule) AS dimensions,
    array_length(schedule, 1) AS first_dim_length,
    array_upper(schedule, 1) AS first_dim_upper,
    array_lower(schedule, 1) AS first_dim_lower,
    cardinality(schedule) AS total_elements
FROM sal_emp
WHERE name = 'Carol';

输出：

 name  | dimensions | first_dim_length | first_dim_upper | first_dim_lower | total_elements
-------+------------+------------------+-----------------+-----------------+----------------
 Carol | [1:2][1:2] |                2 |               2 |               1 |              4

边界处理示例

sql

-- 演示越界访问行为
SELECT
    name,
    pay_by_quarter[5] AS fifth_quarter,    -- 返回 NULL，不报错
    pay_by_quarter[1:10] AS extended_slice  -- 返回实际存在的元素
FROM sal_emp
WHERE name = 'Bill';

8.15.4 修改数组

完整替换数组

sql

-- 完全替换数组值
UPDATE sal_emp
SET pay_by_quarter = '{25000,25000,27000,27000}'
WHERE name = 'Carol';

-- 使用 ARRAY 语法
UPDATE sal_emp
SET pay_by_quarter = ARRAY[26000,26000,28000,28000]
WHERE name = 'Carol';

更新单个元素

sql

-- 更新特定位置的元素
UPDATE sal_emp
SET pay_by_quarter[4] = 15000
WHERE name = 'Bill';

-- 查看更新结果
SELECT name, pay_by_quarter FROM sal_emp WHERE name = 'Bill';

更新数组切片

sql

-- 更新数组的一部分
UPDATE sal_emp
SET pay_by_quarter[1:2] = '{27000,27000}'
WHERE name = 'Carol';

数组扩容

通过为不存在的索引赋值来扩容数组：

sql

-- 演示数组扩容
CREATE TABLE test_array (
    id serial,
    numbers integer[]
);

INSERT INTO test_array (numbers) VALUES ('{1,2,3,4}');

-- 扩容数组（位置5将自动填充NULL）
UPDATE test_array
SET numbers[6] = 100
WHERE id = 1;

SELECT numbers FROM test_array WHERE id = 1;
-- 输出: {1,2,3,4,NULL,100}

自定义下标数组

sql

-- 创建使用非1基索引的数组
CREATE TABLE custom_index (
    data integer[]
);

-- 使用自定义下标范围
INSERT INTO custom_index
VALUES ('[0:2]={10,20,30}');

SELECT
    data,
    array_lower(data, 1) AS lower_bound,
    array_upper(data, 1) AS upper_bound
FROM custom_index;

数组连接操作

PostgreSQL 提供了强大的数组连接功能：

sql

-- 基本连接操作
SELECT ARRAY[1,2] || ARRAY[3,4] AS simple_concat;
-- 输出: {1,2,3,4}

-- 元素与数组连接
SELECT ARRAY[1,2] || 3 AS append_element;
-- 输出: {1,2,3}

SELECT 0 || ARRAY[1,2] AS prepend_element;
-- 输出: {0,1,2}

-- 多维数组连接
SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]] AS multi_dim_concat;
-- 输出: {{5,6},{1,2},{3,4}}

数组操作函数

sql

-- 使用专门的数组函数
SELECT
    array_prepend(0, ARRAY[1,2,3]) AS prepend_result,
    array_append(ARRAY[1,2,3], 4) AS append_result,
    array_cat(ARRAY[1,2], ARRAY[3,4]) AS cat_result;

输出：

 prepend_result | append_result | cat_result
----------------+---------------+------------
 {0,1,2,3}      | {1,2,3,4}     | {1,2,3,4}

8.15.5 在数组中搜索

基本搜索方法

手动检查每个位置（不推荐）

sql

-- 低效的搜索方法
SELECT * FROM sal_emp
WHERE pay_by_quarter[1] = 10000 OR
      pay_by_quarter[2] = 10000 OR
      pay_by_quarter[3] = 10000 OR
      pay_by_quarter[4] = 10000;

使用 ANY 操作符（推荐）

sql

-- 高效的搜索方法
SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);

使用 ALL 操作符

sql

-- 查找所有季度薪水都等于10000的员工
SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

高级搜索技术

使用 generate_subscripts 函数

sql

-- 使用 generate_subscripts 进行灵活搜索
SELECT name, pay_by_quarter, s AS quarter_index
FROM (
    SELECT
        name,
        pay_by_quarter,
        generate_subscripts(pay_by_quarter, 1) AS s
    FROM sal_emp
) AS subscript_data
WHERE pay_by_quarter[s] = 10000;

数组重叠操作

sql

-- 使用 && 操作符检查数组重叠
SELECT name, pay_by_quarter
FROM sal_emp
WHERE pay_by_quarter && ARRAY[10000];

-- 检查多个值的重叠
SELECT name, pay_by_quarter
FROM sal_emp
WHERE pay_by_quarter && ARRAY[10000, 25000];

数组位置查找函数

array_position 和 array_positions

sql

-- 创建示例数据
CREATE TABLE search_demo (
    id serial,
    values integer[],
    days text[]
);

INSERT INTO search_demo (values, days) VALUES
('{1, 4, 3, 1, 3, 4, 2, 1}', '{"sun","mon","tue","wed","thu","fri","sat"}');

-- 查找第一次出现的位置
SELECT array_position(days, 'mon') AS monday_position
FROM search_demo;
-- 输出: 2

-- 查找所有出现的位置
SELECT array_positions(values, 1) AS all_ones_positions
FROM search_demo;
-- 输出: {1,4,8}

复杂搜索示例

sql

-- 查找包含特定模式的二维数组
SELECT name, schedule
FROM sal_emp
WHERE 'meeting' = ANY(
    SELECT unnest(schedule_row)
    FROM unnest(schedule) AS schedule_row
);

-- 使用 EXISTS 进行复杂搜索
SELECT name
FROM sal_emp s1
WHERE EXISTS (
    SELECT 1
    FROM unnest(s1.pay_by_quarter) WITH ORDINALITY AS t(salary, quarter)
    WHERE salary > 20000 AND quarter <= 2
);

搜索性能优化

对于频繁的数组搜索操作，可以考虑：

使用 GIN 索引来加速数组搜索
评估是否应该将数组数据规范化到单独的表中
使用适当的操作符以利用索引

sql

-- 创建 GIN 索引以加速数组搜索
CREATE INDEX idx_pay_quarters_gin ON sal_emp USING GIN (pay_by_quarter);

-- 索引支持的查询类型
SELECT * FROM sal_emp WHERE pay_by_quarter && ARRAY[10000];
SELECT * FROM sal_emp WHERE pay_by_quarter @> ARRAY[10000];
SELECT * FROM sal_emp WHERE ARRAY[10000] <@ pay_by_quarter;

8.15.6 数组输入和输出语法

输出格式规则

PostgreSQL 的数组输出遵循特定的格式规则：

sql

-- 演示各种输出格式
CREATE TABLE output_demo (
    simple_array integer[],
    text_array text[],
    multi_dim integer[][],
    custom_bounds integer[]
);

INSERT INTO output_demo VALUES (
    '{1,2,3}',
    '{"hello", "world", "NULL", ""}',
    '{{1,2},{3,4}}',
    '[0:2]={10,20,30}'
);

SELECT * FROM output_demo;

特殊字符处理

数组输出会自动处理特殊字符：

sql

-- 包含特殊字符的数组
INSERT INTO output_demo (text_array) VALUES (
    '{"text with spaces", "text,with,commas", "text\"with\"quotes", "text\\with\\backslashes"}'
);

自定义下标范围

sql

-- 显示自定义下标范围的数组
SELECT
    custom_bounds,
    array_lower(custom_bounds, 1) AS lower_bound,
    array_upper(custom_bounds, 1) AS upper_bound
FROM output_demo
WHERE custom_bounds IS NOT NULL;

输出：

 custom_bounds | lower_bound | upper_bound
---------------+-------------+-------------
 [0:2]={10,20,30} |           0 |           3

输入语法的灵活性

sql

-- 各种有效的输入格式
INSERT INTO output_demo (simple_array) VALUES
('{ 1 , 2 , 3 }'),                    -- 带空格
('{"1","2","3"}'),                     -- 带引号的数字
('[1:3]={1,2,3}'),                     -- 指定下标范围
('{1,NULL,3}');                        -- 包含 NULL 值

ARRAY 构造函数优势

在 SQL 命令中，ARRAY 构造函数语法通常比数组文字语法更容易使用和理解：

sql

-- 比较两种语法
-- 数组文字语法（需要转义特殊字符）
INSERT INTO output_demo (text_array) VALUES
('{"He said \"Hello\"", "Path: C:\\Users"}');

-- ARRAY 构造函数语法（更清晰）
INSERT INTO output_demo (text_array) VALUES
(ARRAY['He said "Hello"', 'Path: C:\Users']);

实际应用场景

1. 标签系统

sql

-- 文章标签系统
CREATE TABLE articles (
    id serial PRIMARY KEY,
    title text NOT NULL,
    content text,
    tags text[],
    created_at timestamp DEFAULT CURRENT_TIMESTAMP
);

-- 插入带标签的文章
INSERT INTO articles (title, content, tags) VALUES
('PostgreSQL Arrays', 'Content about arrays...', ARRAY['postgresql', 'database', 'arrays']),
('Web Development', 'Content about web dev...', ARRAY['javascript', 'html', 'css', 'web']);

-- 搜索包含特定标签的文章
SELECT title, tags
FROM articles
WHERE 'postgresql' = ANY(tags);

-- 搜索包含多个标签的文章
SELECT title, tags
FROM articles
WHERE tags && ARRAY['database', 'web'];

2. 时间序列数据

sql

-- 传感器数据存储
CREATE TABLE sensor_readings (
    sensor_id text,
    reading_date date,
    hourly_temperatures numeric[24],  -- 24小时温度读数
    daily_summary jsonb
);

-- 插入一天的温度数据
INSERT INTO sensor_readings VALUES (
    'TEMP_001',
    '2024-01-15',
    ARRAY[18.5, 18.2, 17.8, 17.5, 17.2, 17.0, 16.8, 17.2,
          18.5, 20.1, 22.3, 24.5, 26.2, 27.8, 28.1, 27.9,
          26.5, 24.8, 23.2, 21.5, 20.1, 19.2, 18.8, 18.6],
    '{"avg": 21.2, "max": 28.1, "min": 16.8}'
);

-- 查询特定时间段的温度
SELECT
    sensor_id,
    reading_date,
    hourly_temperatures[9:17] AS business_hours_temp  -- 上午9点到下午5点
FROM sensor_readings
WHERE reading_date = '2024-01-15';

3. 权限管理系统

sql

-- 用户权限系统
CREATE TABLE user_permissions (
    user_id uuid PRIMARY KEY,
    username text NOT NULL,
    roles text[],
    permissions text[]
);

-- 插入用户权限
INSERT INTO user_permissions VALUES
(gen_random_uuid(), 'admin_user',
 ARRAY['admin', 'moderator'],
 ARRAY['read', 'write', 'delete', 'manage_users']),
(gen_random_uuid(), 'regular_user',
 ARRAY['user'],
 ARRAY['read', 'write']);

-- 检查用户是否具有特定权限
SELECT username, permissions
FROM user_permissions
WHERE 'delete' = ANY(permissions);

-- 检查用户是否具有管理员角色
SELECT username, roles
FROM user_permissions
WHERE 'admin' = ANY(roles);

性能考量和最佳实践

索引优化

sql

-- 为数组列创建 GIN 索引
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);
CREATE INDEX idx_user_permissions_roles ON user_permissions USING GIN (roles);
CREATE INDEX idx_user_permissions_perms ON user_permissions USING GIN (permissions);

-- 利用索引的查询
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM articles WHERE tags @> ARRAY['postgresql'];

数组 vs 规范化表格对比

使用建议

注意事项

避免大型数组：过大的数组会影响性能，考虑分页或分割策略
索引策略：对于频繁搜索的数组列，使用适当的 GIN 索引
数据验证：在应用层验证数组数据的完整性和格式
备份考虑：大型数组可能影响备份和恢复时间

最佳实践

明确用途：确保数组确实适合您的用例，而不是规范化表格
限制大小：保持数组元素数量在合理范围内（通常少于 100 个元素）
一致性检查：定期验证数组数据的一致性和完整性
文档化：清楚地记录数组结构和预期内容

总结

PostgreSQL 的数组功能为处理相关数据集合提供了强大而灵活的解决方案。通过合理使用数组类型，可以简化某些查询操作并提高存储效率。然而，需要谨慎评估数组是否适合特定的应用场景，并在性能、可维护性和数据完整性之间找到平衡。

数组最适合于存储同质化的、相对稳定的数据集合，如标签、选项列表或时间序列数据。对于需要复杂查询、频繁更新或严格关系约束的数据，传统的规范化表格结构可能是更好的选择。

找到具有 1 个许可证类型的类似代码

arrays ​

数组概述 ​

8.15.1 数组类型的声明 ​

基本语法 ​

多维数组声明 ​

SQL 标准语法 ​

数组特性说明 ​

8.15.2 数组值输入 ​

文字常量语法 ​

查看插入结果 ​

ARRAY 构造函数语法 ​

多维数组要求 ​

NULL 值处理 ​

8.15.3 访问数组 ​

单元素访问 ​

数组切片操作 ​

切片边界处理 ​

数组维度信息函数 ​

边界处理示例 ​

8.15.4 修改数组 ​

完整替换数组 ​

更新单个元素 ​

更新数组切片 ​

数组扩容 ​

自定义下标数组 ​

数组连接操作 ​

数组操作函数 ​

8.15.5 在数组中搜索 ​

基本搜索方法 ​

手动检查每个位置（不推荐） ​

使用 ANY 操作符（推荐） ​

使用 ALL 操作符 ​

高级搜索技术 ​

使用 generate_subscripts 函数 ​

数组重叠操作 ​

数组位置查找函数 ​

array_position 和 array_positions ​

复杂搜索示例 ​

搜索性能优化 ​

8.15.6 数组输入和输出语法 ​

输出格式规则 ​

特殊字符处理 ​

自定义下标范围 ​

输入语法的灵活性 ​

ARRAY 构造函数优势 ​

实际应用场景 ​

1. 标签系统 ​

2. 时间序列数据 ​

3. 权限管理系统 ​

性能考量和最佳实践 ​

索引优化 ​

数组 vs 规范化表格对比 ​

使用建议 ​

总结 ​

arrays

数组概述

8.15.1 数组类型的声明

基本语法

多维数组声明

SQL 标准语法

数组特性说明

8.15.2 数组值输入

文字常量语法

查看插入结果

ARRAY 构造函数语法

多维数组要求

NULL 值处理

8.15.3 访问数组

单元素访问

数组切片操作

切片边界处理

数组维度信息函数

边界处理示例

8.15.4 修改数组

完整替换数组

更新单个元素

更新数组切片

数组扩容

自定义下标数组

数组连接操作

数组操作函数

8.15.5 在数组中搜索

基本搜索方法

手动检查每个位置（不推荐）

使用 ANY 操作符（推荐）

使用 ALL 操作符

高级搜索技术

使用 generate_subscripts 函数

数组重叠操作

数组位置查找函数

array_position 和 array_positions

复杂搜索示例

搜索性能优化

8.15.6 数组输入和输出语法

输出格式规则

特殊字符处理

自定义下标范围

输入语法的灵活性

ARRAY 构造函数优势

实际应用场景

1. 标签系统

2. 时间序列数据

3. 权限管理系统

性能考量和最佳实践

索引优化

数组 vs 规范化表格对比

使用建议

总结