PgSQL各种数组类型和用法示例

PgSQL数组类型与用法详解

原创于 2025-09-17 20:44:42 发布 · 481 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库 #postgresql

Postgresql数据库专栏收录该内容

36 篇文章

订阅专栏

PostgreSQL 的数组类型功能非常强大，支持多维数组、灵活查询、函数操作。

1. 定义数组字段

-- 一维数组
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT,
    tags TEXT[]    -- 定义字符串数组
);

-- 多维数组
CREATE TABLE matrix_data (
    id SERIAL PRIMARY KEY,
    matrix INTEGER[][]   -- 定义二维数组
);

2. 插入数组数据

-- 插入一维数组
INSERT INTO users (name, tags)
VALUES ('Alice', ARRAY['developer', 'gamer', 'blogger']);

-- 插入多维数组
INSERT INTO matrix_data (matrix)
VALUES ('{{1,2,3},{4,5,6},{7,8,9}}');

3. 查询数组

-- 查询数组字段
SELECT name, tags FROM users;

-- 获取数组的第一个元素（下标从 1 开始）
SELECT tags[1] FROM users WHERE name = 'Alice';

-- 获取数组的长度
SELECT array_length(tags, 1) FROM users WHERE name = 'Alice';

-- 查询包含某个元素的行
SELECT * FROM users WHERE 'gamer' = ANY(tags);

-- 查询数组是否包含某些元素
SELECT * FROM users WHERE tags @> ARRAY['developer'];  -- 包含
SELECT * FROM users WHERE tags && ARRAY['teacher','gamer'];  -- 有交集

4. 修改数组

-- 更新数组：添加新元素
UPDATE users SET tags = array_append(tags, 'teacher') WHERE name = 'Alice';

-- 删除数组中的某个元素
UPDATE users SET tags = array_remove(tags, 'blogger') WHERE name = 'Alice';

-- 替换数组中的某个位置
UPDATE users SET tags[2] = 'writer' WHERE name = 'Alice';

5. 常用数组函数

-- 拼接两个数组
SELECT ARRAY[1,2,3] || ARRAY[4,5];  -- {1,2,3,4,5}

-- 去重 (需要结合 unnest + DISTINCT)
SELECT ARRAY(SELECT DISTINCT unnest(ARRAY[1,2,2,3,3,4]));

-- 展开数组为多行
SELECT unnest(tags) FROM users WHERE name='Alice';

-- 数组排序
SELECT array_agg(tag ORDER BY tag) 
FROM unnest(ARRAY['b','c','a','c']) AS tag;

-- 生成序列数组
SELECT ARRAY[1:5];  -- 错误 ❌
SELECT ARRAY(SELECT generate_series(1,5));  -- 正确，结果 {1,2,3,4,5}

6. 多维数组操作

-- 取二维数组中的元素
SELECT matrix[2][3] FROM matrix_data;  -- 取第2行第3列的值

-- 更新二维数组某个元素
UPDATE matrix_data SET matrix[1][2] = 99 WHERE id = 1;

7. 索引与性能优化

数组字段可以建 GIN 索引，提高查询效率：

-- 为数组字段建索引
CREATE INDEX idx_users_tags ON users USING gin (tags);

-- 使用索引的查询
SELECT * FROM users WHERE tags @> ARRAY['developer'];

✅ 总结：

ARRAY[] 定义数组；
ANY / @> / && 用于数组条件查询；
unnest() 用于展开；
array_append / array_remove 用于修改；
支持多维数组和 GIN 索引优化。

📝 PostgreSQL 数组用法速查表

1. 定义数组

-- 一维数组
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT,
    tags TEXT[]
);

-- 多维数组
CREATE TABLE matrix_data (
    id SERIAL PRIMARY KEY,
    matrix INTEGER[][]
);

2. 插入数据

-- 一维数组
INSERT INTO users (name, tags)
VALUES ('Alice', ARRAY['developer', 'gamer', 'blogger']);

-- 二维数组
INSERT INTO matrix_data (matrix)
VALUES ('{{1,2,3},{4,5,6},{7,8,9}}');

3. 查询数组

-- 取第一个元素 (下标从 1 开始)
SELECT tags[1] FROM users;

-- 获取数组长度
SELECT array_length(tags, 1) FROM users;

-- 查询数组是否包含某元素
SELECT * FROM users WHERE 'gamer' = ANY(tags);

-- 查询数组是否包含某些元素
SELECT * FROM users WHERE tags @> ARRAY['developer'];   -- 包含
SELECT * FROM users WHERE tags && ARRAY['teacher','gamer'];  -- 有交集

4. 修改数组

-- 添加元素
UPDATE users SET tags = array_append(tags, 'teacher') WHERE name='Alice';

-- 删除元素
UPDATE users SET tags = array_remove(tags, 'blogger') WHERE name='Alice';

-- 替换指定位置
UPDATE users SET tags[2] = 'writer' WHERE name='Alice';

5. 常用函数

-- 拼接数组
SELECT ARRAY[1,2,3] || ARRAY[4,5];    -- {1,2,3,4,5}

-- 去重
SELECT ARRAY(SELECT DISTINCT unnest(ARRAY[1,2,2,3,3,4]));

-- 展开为多行
SELECT unnest(tags) FROM users;

-- 排序
SELECT array_agg(tag ORDER BY tag)
FROM unnest(ARRAY['b','c','a','c']) AS tag;

-- 生成序列数组
SELECT ARRAY(SELECT generate_series(1,5));  -- {1,2,3,4,5}

6. 多维数组

-- 取二维数组中的元素
SELECT matrix[2][3] FROM matrix_data;  -- 第2行第3列

-- 更新二维数组元素
UPDATE matrix_data SET matrix[1][2] = 99 WHERE id=1;

7. 索引优化

-- GIN 索引 (适合数组查询)
CREATE INDEX idx_users_tags ON users USING gin (tags);

-- 使用索引的查询
SELECT * FROM users WHERE tags @> ARRAY['developer'];

✅ 快速记忆：

定义：TEXT[]、INTEGER[][]
插入：ARRAY['a','b'] 或 '{{1,2},{3,4}}'
查询：ANY、@>、&&
修改：array_append、array_remove、tags[2] = 'xxx'
展开：unnest()
优化：GIN 索引