PgSQL数组类型复杂用法示例

整理一份 PostgreSQL 数组类型各种复杂用法示例，涵盖 定义、插入、查询、修改、函数操作、索引优化等常见复杂场景。

---

1. 创建数组字段

-- 创建一个表，包含整型数组和文本数组
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT,
    scores INT[],         -- 整型数组
    tags TEXT[]           -- 文本数组
);

---

2. 插入数组数据

-- 插入数组时可以用 {} 或 ARRAY
INSERT INTO users (name, scores, tags)
VALUES
('Alice', '{90,85,88}', '{"dev","teamA"}'),
('Bob',   ARRAY[75,80,92], ARRAY['test','teamB']);

---

3. 取出数组元素

-- 下标从 1 开始（注意：不是 0）
SELECT name, scores[1] AS first_score, tags[2] AS second_tag
FROM users;

-- 获取数组的长度
SELECT name, array_length(scores, 1) AS score_count
FROM users;

---

4. 更新数组内容

-- 修改单个元素
UPDATE users
SET scores[2] = 99
WHERE name = 'Alice';

-- 给数组追加一个元素
UPDATE users
SET tags = array_append(tags, 'leader')
WHERE name = 'Bob';

-- 在数组开头插入一个元素
UPDATE users
SET scores = array_prepend(100, scores)
WHERE name = 'Alice';

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

---

5. 查询数组包含关系

-- 包含某个元素
SELECT * FROM users
WHERE 'dev' = ANY(tags);

-- 同时包含多个元素
SELECT * FROM users
WHERE tags @> ARRAY['dev','teamA'];

-- 是否被包含
SELECT * FROM users
WHERE tags <@ ARRAY['dev','teamA','leader'];

---

6. 数组与聚合函数

-- 聚合后合并数组
SELECT array_agg(name) AS all_users
FROM users;

-- 多行转数组
SELECT id, array_agg(scores) AS all_scores
FROM users
GROUP BY id;

---

7. 数组与 UNNEST

-- 将数组展开成多行
SELECT name, unnest(scores) AS score
FROM users;

-- 多个数组同时展开
SELECT name, unnest(scores), unnest(tags)
FROM users;

---

8. 多维数组

-- 插入二维数组
INSERT INTO users (name, scores, tags)
VALUES ('Chris', '{{70,80,90},{85,95,100}}', '{"multi","matrix"}');

-- 取出二维数组某个元素
SELECT scores[2][3] AS last_value FROM users WHERE name='Chris';

---

9. 常用数组函数

-- 拼接数组
SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);  -- {1,2,3,4}

-- 去重 (先 UNNEST 再 DISTINCT)
SELECT array_agg(DISTINCT x ORDER BY x)
FROM unnest(ARRAY[1,2,2,3,4,4]) x;

-- 数组切片
SELECT scores[1:2] FROM users WHERE name='Alice';  -- 前两个分数

---

10. 索引优化（GIN 索引）

-- 建立 GIN 索引，加速数组包含查询
CREATE INDEX idx_users_tags ON users USING GIN (tags);

-- 查询包含关系（会走索引）
SELECT * FROM users WHERE tags @> ARRAY['dev'];

---

实际业务场景案例

🛠️ 场景 1：用户兴趣爱好系统

用户可能有多个兴趣爱好，存放在数组字段里。

CREATE TABLE user_profile (
    id SERIAL PRIMARY KEY,
    name TEXT,
    hobbies TEXT[]
);

-- 插入示例数据
INSERT INTO user_profile (name, hobbies) VALUES
('Alice', '{"reading","travel","music"}'),
('Bob',   '{"sports","music","gaming"}'),
('Chris', '{"cooking","travel","sports"}');

查询案例

-- 找出喜欢 "music" 的用户
SELECT * FROM user_profile WHERE 'music' = ANY(hobbies);

-- 找出同时喜欢 "travel" 和 "sports" 的用户
SELECT * FROM user_profile WHERE hobbies @> ARRAY['travel','sports'];

-- 给用户新增一个爱好（避免重复可结合 array_append + distinct）
UPDATE user_profile
SET hobbies = array_append(hobbies, 'art')
WHERE name = 'Alice';

---

🛠️ 场景 2：订单状态流转记录

订单可能会经历多个状态（如 created → paid → shipped → delivered），用数组记录状态历史。

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer TEXT,
    status_history TEXT[]
);

INSERT INTO orders (customer, status_history) VALUES
('Alice', '{"created","paid"}'),
('Bob',   '{"created"}');

更新与查询

-- 订单支付完成，追加状态
UPDATE orders
SET status_history = array_append(status_history, 'shipped')
WHERE id = 1;

-- 查询订单是否已进入 "shipped" 状态
SELECT * FROM orders WHERE 'shipped' = ANY(status_history);

-- 查询已经包含 "paid" 但还没有 "delivered" 的订单
SELECT * FROM orders
WHERE status_history @> ARRAY['paid']
  AND NOT status_history @> ARRAY['delivered'];

---

🛠️ 场景 3：日志系统关键字存储

日志表中存储一些关键字（例如：error, timeout, db），方便检索。

CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    message TEXT,
    keywords TEXT[]
);

INSERT INTO logs (message, keywords) VALUES
('Database connection timeout', '{"error","timeout","db"}'),
('User login success', '{"info","auth"}'),
('Payment gateway error', '{"error","payment"}');

查询案例

-- 快速查找包含 "error" 的日志
SELECT * FROM logs WHERE 'error' = ANY(keywords);

-- 查找包含多个关键字的日志
SELECT * FROM logs WHERE keywords @> ARRAY['db','timeout'];

-- 聚合：统计出现过的所有关键字
SELECT array_agg(DISTINCT unnest(keywords)) AS all_keywords
FROM logs;

⚡ 性能优化：为 keywords 建立 GIN 索引

CREATE INDEX idx_logs_keywords ON logs USING GIN (keywords);

---

🛠️ 场景 4：多维数组存储成绩

一个学生多门课程，每门课多次考试，二维数组可以保存。

CREATE TABLE student_scores (
    id SERIAL PRIMARY KEY,
    name TEXT,
    scores INT[][]
);

INSERT INTO student_scores (name, scores) VALUES
('Alice', '{{90,85,80},{88,92,95}}'),
('Bob',   '{{70,75,80},{85,87,89}}');

查询案例

-- 获取 Alice 第二门课程的成绩
SELECT scores[2] FROM student_scores WHERE name='Alice';

-- 获取 Bob 第一门课程的第二次成绩
SELECT scores[1][2] FROM student_scores WHERE name='Bob';

---

✅ 上面这 4 个场景展示了 PostgreSQL 数组在实际业务中的高效用法：

• 用户兴趣爱好（标签类场景）
• 订单状态流转（历史追踪）
• 日志关键字（快速检索+索引优化）
• 学生成绩（多维数组结构化存储）