使用陣列時常見錯誤及其預防方法

今天我們要結束這趟超有趣的 PostgreSQL 陣列之旅。今天的主題就是你可能會遇到的常見錯誤，還有最重要的——怎麼避免它們。如果你曾經心裡想過「這個陣列又在搞什麼鬼」，那這堂課就是為你準備的。來，咱們一起搞懂它。

建立陣列時的錯誤：陣列內部資料型別的問題

你在 PostgreSQL 建立陣列時要記得，所有元素都要是同一種型別。舉個例子：

SELECT ARRAY[1, 2, '三'];
-- 錯誤：陣列裡的元素必須是同一型別

PostgreSQL 不會讓你把數字跟字串混在同一個陣列裡。如果真的有這需求，就要做型別轉換：

SELECT ARRAY[1::TEXT, 2::TEXT, '三'];
-- 現在這個陣列全都是字串

使用 ARRAY[] 搭配不同資料型別時的錯誤

預設情況下，PostgreSQL 會根據內容來判斷陣列型別。如果你給它一個不明確的陣列，就要準備好看到錯誤：

SELECT ARRAY[1, NULL];
-- 錯誤：PostgreSQL 不知道該怎麼解讀 NULL

要解決這個問題，就要明確指定資料型別：

SELECT ARRAY[1, NULL]::INTEGER[];
-- 這樣就沒問題了

取出資料時的錯誤：陣列索引的問題

如果你習慣用 Python 或 JavaScript 寫程式，陣列索引都是從零開始，但 PostgreSQL 會給你一個驚喜——這裡的陣列索引是從 1 開始的。

SELECT ARRAY[10, 20, 30][0];
-- 錯誤：索引必須從 1 開始

正確的查詢：

SELECT ARRAY[10, 20, 30][1];
-- 結果：10

使用函式取值時的錯誤

像 unnest() 這種函式很容易讓人搞混，因為它會把陣列「攤平」成多行：

CREATE TEMP TABLE example (
    id SERIAL PRIMARY KEY,
    tags TEXT[]
);

INSERT INTO example (tags) VALUES (ARRAY['標籤1', '標籤2']), (ARRAY['標籤3']);

SELECT unnest(tags) FROM example;
-- 結果：
-- 標籤1
-- 標籤2
-- 標籤3

如果你想保留每一行的上下文（像是 id），就要把它一起查出來：

SELECT id, unnest(tags) AS tag FROM example;
-- 結果：
-- id | tag
--  1 | 標籤1
--  1 | 標籤2
--  2 | 標籤3

過濾和比較陣列時的錯誤

1. 錯誤使用 @>、<@、&& 這些運算子

這些運算子是有特定用途的：

• @> 檢查一個陣列是否包含另一個陣列。
• <@ 檢查一個陣列是否被包含在另一個陣列裡。
• && 檢查兩個陣列有沒有交集。

如果你亂用它們就會出錯：

SELECT ARRAY[1, 2, 3] @> 2;
-- 錯誤：@> 運算子只能用在陣列

正確用法：

SELECT ARRAY[1, 2, 3] @> ARRAY[2];
-- 結果：true

2. 沒有建立索引導致效能問題

如果你常用陣列運算子，發現查詢變慢，很可能是你忘了加索引。這裡有個建立陣列索引的範例：

CREATE INDEX idx_tags ON example USING GIN (tags);

這樣用 @> 跟 && 的查詢就會快很多。

修改陣列時的錯誤

1. 刪除和新增元素

array_remove() 跟 array_append() 這些函式不會「原地」改變陣列，它們會回傳一個新的陣列。如果你以為原本的陣列會直接變，那就錯了：

UPDATE example
SET tags = array_remove(tags, '標籤1');
-- 現在陣列已經更新了

如果你忘了寫 SET tags =，SQL 會執行，但陣列根本沒變。

2. 用 array_append() 會重複加資料

array_append() 不會檢查元素有沒有重複，所以可能會加出重複的值：

SELECT array_append(ARRAY['標籤1', '標籤2'], '標籤1');
-- 結果：{標籤1, 標籤2, 標籤1}

如果你不想要重複，就要自己過濾一下：

SELECT array_remove(array_append(ARRAY['標籤1', '標籤2'], '標籤1'), '標籤1') || '標籤1';
-- 結果：{標籤1, 標籤2}

預防錯誤的小建議

想要避免上面那些陣列的坑，可以這樣做：

1. 檢查資料型別。如果型別不明確，記得明確指定陣列型別。
2. 索引要小心。記住 PostgreSQL 的陣列索引是從 1 開始的。
3. 優化陣列查詢。用索引來加速比較和過濾。
4. 先用小資料測試。如果查詢很慢，先用小一點的資料集找出瓶頸。
5. 避免重複。加元素時如果不能重複，記得多做檢查。

PostgreSQL 的陣列就像一把很強的武器，用的時候要小心又要尊重它。照著這些建議走，你的陣列就不會再讓你失眠啦（大概啦）。祝你設計和優化資料庫順利！