在PostgreSQL中玩转数组

在PostgreSQL中玩转数组

现在我们已经搞懂了基础，来点实际操作，看看怎么在SQL查询里造数组。这里才是真正好玩的地方！

在SELECT里用ARRAY[]构造器

ARRAY[]构造器在SELECT查询里特别方便，想直接造个数组就用它。就像跟PostgreSQL说：“嘿，给我个数组！”

-- 创建数字数组
SELECT ARRAY[1, 2, 3, 4, 5] AS numbers;

-- 创建字符串数组
SELECT ARRAY['星期一', '星期二', '星期三'] AS weekdays;

ARRAY[]比{}语法的优势

1. 可以显式指定类型：

-- 用ARRAY[]可以直接指定类型
SELECT ARRAY['2023-01-01'::DATE, '2023-12-31'::DATE] AS dates;

-- 用{}要小心点
SELECT '{"2023-01-01", "2023-12-31"}'::DATE[] AS dates;

2. 在复杂查询里更易读：

SELECT
product_name,
ARRAY[category, subcategory, brand] AS product_hierarchy
FROM products;

例子：创建数字数组

先来个经典的。假如你要造个数字数组：

SELECT ARRAY[1, 2, 3, 4, 5] AS my_array;

结果会是这样：

注意：PostgreSQL会用{}格式返回数组——这就是它展示数组的方式。风格有点特别，但很快就习惯了。

例子：创建字符串数组

如果你想要字符串而不是数字，只要加上引号：

SELECT ARRAY['苹果', '香蕉', '橙子'] AS fruits;

结果：

顺便说一句，PostgreSQL超喜欢让生活变简单。就算你用汉字或者别的字母表，数组也一样稳稳地用。

例子：其他类型的数据数组（比如日期）

那如果我们想放一堆日期进数组呢？超级简单：

SELECT ARRAY['2023-01-01'::DATE, '2023-12-31'::DATE] AS important_dates;

结果：

注意::DATE。我们明确告诉PostgreSQL这是DATE类型。不然它可能就直接当字符串处理了，这对日期来说就不太对劲。

用array_agg()把数据聚成数组

现在来点更高级的。如果我们已经有一张表，要把数据按组聚成数组怎么办？这时候array_agg()就派上用场了。

最强大的功能之一，就是用array_agg()把多行变成数组。

基本用法：

-- 先造个测试表
CREATE TEMP TABLE students (
group_id INTEGER,
student_name TEXT
);

INSERT INTO students VALUES
(1, '安娜'), (1, '奥托'), (1, '玛丽亚'),
(2, '阿列克斯'), (2, '基拉'),
(3, '叶莲娜');

-- 按组聚合学生
SELECT
group_id,
array_agg(student_name) AS students
FROM students
GROUP BY group_id
ORDER BY group_id;

数组里的元素排序：

SELECT
group_id,
array_agg(student_name ORDER BY student_name) AS students_sorted
FROM students
GROUP BY group_id;

聚合时过滤：

SELECT
group_id,
array_agg(student_name) FILTER (WHERE student_name LIKE '阿%') AS students_a
FROM students
GROUP BY group_id;

实际用法举例

数组在很多日常场景都很有用：比如存标签、权限，或者收集用户一天的操作。下面这些例子能帮你更好理解PostgreSQL里数组的用法。

例子1：博客的标签系统

CREATE TABLE blog_posts (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    tags TEXT[]
);

-- 用不同语法插入
INSERT INTO blog_posts (title, content, tags) VALUES
    ('学习PostgreSQL', '文章内容...',
     ARRAY['PostgreSQL', 'SQL', '数据库']),
    ('2024年Web开发', '文章内容...',
     '{"JavaScript", "React", "Node.js"}'),
    ('机器学习', '文章内容...',
     ARRAY['ML', 'Python', 'Data Science']);

-- 按标签查找文章
SELECT title FROM blog_posts
WHERE 'PostgreSQL' = ANY(tags);

例子2：用户权限系统

CREATE TABLE users (
id SERIAL PRIMARY KEY,
username TEXT NOT NULL,
permissions TEXT[]
);

INSERT INTO users (username, permissions) VALUES
('admin', ARRAY['read', 'write', 'delete', 'manage_users']),
('editor', ARRAY['read', 'write']),
('viewer', ARRAY['read']);

-- 聚合系统里所有唯一权限
SELECT array_agg(DISTINCT permission) AS all_permissions
FROM users, unnest(permissions) AS permission;

例子3：用户操作历史

CREATE TABLE user_actions (
user_id INTEGER,
action TEXT,
action_date DATE
);

INSERT INTO user_actions VALUES
(1, '登录', '2024-01-01'),
(1, '查看个人资料', '2024-01-01'),
(1, '编辑设置', '2024-01-01'),
(2, '登录', '2024-01-01'),
(2, '登出', '2024-01-01');

-- 按天聚合用户操作
SELECT
user_id,
action_date,
array_agg(action ORDER BY action) AS daily_actions
FROM user_actions
GROUP BY user_id, action_date
ORDER BY user_id, action_date;

4. 数组查询：选择和过滤

有了数组之后，我们还得会查和分析它们。你可以用标准SELECT来拿到数组：

SELECT tags FROM articles WHERE id = 1;

这会返回：

但如果我们想找一篇有特定标签，比如PostgreSQL的文章怎么办？这个我们下节课会详细讲，不过核心思想很简单：数组让我们可以灵活地在里面查值。