在 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() 聚合資料成陣列

接下來進階一點。如果你已經有一張資料表，想把資料 group 起來變成陣列？這時候 array_agg() 就超好用。

這功能超強——可以把一堆 row 變成一個陣列，靠 array_agg() 就搞定。

基本用法：

-- 先創一個測試用的表
CREATE TEMP TABLE students (
group_id INTEGER,
student_name TEXT
);

INSERT INTO students VALUES
(1, '安娜'), (1, '奧托'), (1, '瑪麗亞'),
(2, '亞歷克斯'), (2, '奇拉'),
(3, '葉蓮娜');

-- 依 group 把學生 group 起來
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;

實戰應用範例

陣列在很多日常場景都超好用：像是存 tag、權限，或是收集用戶一天的行為。下面這些例子可以幫你更懂怎麼在 PostgreSQL 裡用陣列。

範例 1：部落格的 tag 系統

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 網頁開發', '文章內容...',
     '{"JavaScript", "React", "Node.js"}'),
    ('機器學習', '文章內容...',
     ARRAY['ML', 'Python', 'Data Science']);

-- 用 tag 搜文章
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');

-- 依天 group 用戶行為
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;

這會回傳：

但如果你想找有特定 tag（像 PostgreSQL）的文章怎麼辦？這個我們下堂課會細講，不過重點就是：陣列讓你查裡面的值超彈性超方便。