插入和更新資料時的錯誤處理

來看看在 table 插入新資料時常見的錯誤吧。

錯誤 1：嘗試插入 NULL 到必填欄位

PostgreSQL 會很認真地檢查資料庫的規則有沒有被遵守。來看一下可能會出錯的 constraint 範例：

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL, -- 名字不能是 NULL
    age INT
);

-- 錯誤：name 欄位是必填的
INSERT INTO students (name, age) VALUES (NULL, 20);

結果：錯誤 null value in column "name" of relation "students" violates not-null constraint`。

你要注意自己加了什麼資料。有可能這個欄位以前可以是 NULL，但現在已經是必填了。

錯誤 2：在唯一欄位插入重複資料。

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name TEXT UNIQUE -- 課程名稱必須唯一
);

-- 第一次插入 OK
INSERT INTO courses (course_name) VALUES ('SQL Basics');

-- 第二次插入會出錯
INSERT INTO courses (course_name) VALUES ('SQL Basics');

結果：錯誤 duplicate key value violates unique constraint`。

通常這不是你的錯，是 user 不小心重複操作了。這種情況不用特別處理。

錯誤 3：違反參照完整性。

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(id), -- 必須有這個 ID 的學生
    course_id INT REFERENCES courses(course_id)
);

-- 錯誤：ID = 99 的學生不存在
INSERT INTO enrollments (student_id, course_id) VALUES (99, 1);

結果：錯誤 insert or update on table "enrollments" violates foreign key constraint`。

其實出錯是好事。資料庫完整性被破壞才糟糕。大概是你操作資料庫的程式有 bug，或是資料過時了。總之，資料庫不讓你破壞完整性，這超棒的。

PostgreSQL 的錯誤處理

對啦，錯誤一定會發生。但重點不是發現錯誤，而是要會處理它們。

transaction 是保護資料的好工具

我們在操作資料時常常會用 transaction 來確保資料一致。如果出錯，可以直接 rollback。

範例：同時新增兩個 table 的資料。

BEGIN; -- 開始 transaction

-- 插入 students table
INSERT INTO students (name, age) VALUES ('Otto Lin', 21);

-- 插入 enrollments table
-- 如果 ID=10 的課程不存在，這裡會出錯
INSERT INTO enrollments (student_id, course_id) VALUES (1, 10);

-- 如果都成功
COMMIT;
-- 如果有錯誤，"回滾" 變更
ROLLBACK;

如果 course_id = 10 的課程不存在，students table 的插入也會被取消。

在 transaction 裡處理錯誤

在 PostgreSQL 你可以預期錯誤，然後用 EXCEPTION 區塊直接在 SQL 裡處理。

範例：新增學生並讓他選課。如果出錯，把錯誤寫進 log。

DO $$
BEGIN
    -- 嘗試插入資料
    INSERT INTO students (name, age) VALUES ('Anna Song', 22);
    INSERT INTO enrollments (student_id, course_id) VALUES (2, 999); -- 錯誤

    -- 如果都成功
    RAISE NOTICE '紀錄已成功新增！';
EXCEPTION
    WHEN foreign_key_violation THEN
        -- 處理外鍵違規
        RAISE WARNING '指定的 course_id 沒有這個課程。';
END $$;

用 ON CONFLICT 檢查唯一性

你可以用 ON CONFLICT 這個語法，預防 UNIQUE constraint 出錯。這樣可以指定遇到衝突時要怎麼處理。

範例：如果插入重複課程就跳過。

INSERT INTO courses (course_name)
    VALUES ('SQL Basics')
ON CONFLICT (course_name) DO NOTHING; -- 跳過重複資料

或者更新已存在的 row：

INSERT INTO courses (course_name)
    VALUES ('SQL Basics')
ON CONFLICT (course_name) DO UPDATE     
    SET course_name = EXCLUDED.course_name || ' (Updated)';

更多 ON CONFLICT 的用法我會在下一個 level 講，等我們學到 bulk load 的時候 :P

常見資料錯誤與預防方法

你已經看到，錯誤的主要來源有：

1. 違反 constraint（NOT NULL、UNIQUE、FOREIGN KEY）。
2. 在 update 或 delete 時沒加 WHERE 條件。
3. transaction 執行順序錯誤。

想保護自己：

• 大操作用 transaction 和 ROLLBACK。
• 插入前一定要檢查資料。
• 錯誤要寫 log 方便 debug。
• 用 ON CONFLICT 避免重複資料。

現在你已經有對抗錯誤的知識啦！記住：厲害的工程師不是不會出錯，而是會修錯！