使用 `ON CONFLICT` 處理資料載入時的錯誤

歡迎來到批量資料載入最戲劇化的場景中心！今天我們要學會怎麼用 ON CONFLICT 這個語法，超有效率地處理資料載入時發生的錯誤。這就像你在飛機上開啟自動駕駛一樣：就算出包，你也知道怎麼避免災難。來吧，讓我們一起破解 PostgreSQL 的小心機！

沒有人喜歡驚喜，尤其是當資料死都不肯載進來的時候！在批量載入的過程中，你可能會遇到幾種常見的問題：

• 資料重複。 比方說，如果你的資料表有 UNIQUE 限制，而你的資料檔案裡一堆重複值。
• 限制衝突。 比如你硬要塞空值進 NOT NULL 限制的欄位。結果？錯誤。PostgreSQL 在這種情況下可是很嚴格的。
• 主鍵重複。 資料表裡可能早就有跟你 CSV 檔案一樣 id 的資料。

來看看怎麼用 ON CONFLICT 避開這些「地雷」。

用 ON CONFLICT 處理錯誤

ON CONFLICT 這個語法可以讓你指定遇到限制衝突（像 UNIQUE 或 PRIMARY KEY）時要怎麼辦。PostgreSQL 讓你可以選擇更新現有資料，或是直接忽略有衝突的那一列。

這是 ON CONFLICT 的基本語法：

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_target)
DO UPDATE SET column1 = new_value1, column2 = new_value2;

如果你只想忽略衝突，可以把 DO UPDATE 換成 DO NOTHING。

範例：遇到衝突時更新資料

假設我們有一個 students 資料表：

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age INT
);

現在我們想載入新資料，但有些資料其實已經在資料庫裡了：

INSERT INTO students (id, name, age)
VALUES 
    (1, 'Peter', 22),  -- 這個學生已經存在
    (2, 'Anna', 20),  -- 新學生
    (3, 'Mal', 25) -- 新學生
ON CONFLICT (id) DO UPDATE SET 
    name = EXCLUDED.name, 
    age = EXCLUDED.age;

這個例子裡，如果已經有同樣 ID 的學生，資料就會被更新：

ON CONFLICT (id) DO UPDATE SET
    name = EXCLUDED.name, 
    age = EXCLUDED.age;

注意這個神奇的 EXCLUDED。它的意思是「你本來想插入但因為衝突被排除的值」。

結果：

• id = 1 的學生會更新名字和年齡。
• id = 2 和 id = 3 的學生會被加進資料表。

範例：忽略衝突

如果你不想更新資料，只想跳過有衝突的列，就用 DO NOTHING：

INSERT INTO students (id, name, age)
VALUES 
    (1, 'Peter', 22),  -- 這個學生已經存在
    (2, 'Anna', 20),  -- 新學生
    (3, 'Mal', 25) -- 新學生
ON CONFLICT (id) DO NOTHING;

這樣有衝突的列就不會被插入，其他的資料就會乖乖進資料庫。

錯誤紀錄（Log）

有時候只更新或忽略還不夠。比如你想把衝突記錄下來，方便之後分析。我們可以建一個專門記錄錯誤的資料表：

CREATE TABLE conflict_log (
    conflict_time TIMESTAMP DEFAULT NOW(),
    id INT,
    name TEXT,
    age INT,
    conflict_reason TEXT
);

接著加上錯誤紀錄的處理：

INSERT INTO students (id, name, age)
VALUES 
    (1, 'Peter', 22), 
    (2, 'Anna', 20), 
    (3, 'Mal', 25)
ON CONFLICT (id) DO UPDATE SET 
    name = EXCLUDED.name, 
    age = EXCLUDED.age
RETURNING EXCLUDED.id, EXCLUDED.name, EXCLUDED.age
INTO conflict_log;

最後這個例子只有在 儲存程序 裡才會動。詳細怎麼做等我們學 PL-SQL 的時候會講，這裡先小小劇透一下，讓你知道還有一種解法：把所有有問題的資料列都記錄下來。

這樣你就能分析衝突的原因。這招在複雜系統裡超有用，因為批量載入時保留「痕跡」很重要。

實戰範例

來把我們學到的東西合體做個小任務。假設你有一個學生更新的 CSV 檔，要載入到資料表裡：

檔案 students_update.csv

資料載入與衝突處理

1. 先建立一個暫存資料表 tmp_students：

CREATE TEMP TABLE tmp_students (
  id   INTEGER,
  name TEXT,
  age  INTEGER
);

2. 用 \COPY 從檔案載入資料：

\COPY tmp_students FROM 'students_update.csv' DELIMITER ',' CSV HEADER

3. 用 INSERT ON CONFLICT 把暫存表的資料塞進正式表：

INSERT INTO students (id, name, age)
SELECT id, name, age FROM tmp_students
ON CONFLICT (id) DO UPDATE
  SET name = EXCLUDED.name,
      age = EXCLUDED.age;

這樣所有資料（包括 id = 1 的更新）都會順利載入。

常見錯誤與避免方法

就算是老手也會出錯，但知道怎麼避開這些坑，你就能省下超多時間跟心力。

• 跟 UNIQUE 限制衝突。 要確定你在 ON CONFLICT 裡指定的是正確的欄位。比如你本來該用 email，結果寫成 id，PostgreSQL 會直接把你的查詢打槍。
• 錯用 EXCLUDED。 這個別名只對這次查詢帶進來的值有用，別拿去其他地方用。
• 欄位漏掉。 要確定你在 SET 裡寫的欄位都真的存在。像你寫 SET non_existing_column = 'value'，就會出錯。

用 ON CONFLICT 讓你在 PostgreSQL 批量載入資料時又彈性又安全。不只可以避免因為衝突導致查詢失敗，還能完全掌控資料怎麼處理。你的使用者（還有伺服器！）都會感謝你啦。