PostgreSQL 實現 MySQL INSERT IGNORE 語義的 4 種方案：RULE、Trigger、CTE 與 Function

背景

MySQL INSERT IGNORE INTO ... 遇到主鍵/唯一鍵衝突時**靜默忽略**，不報錯、不中斷。PostgreSQL 無直接對應語法 (PG 15+ 支援 ON CONFLICT DO NOTHING)，舊版需用以下替代方案。

測試環境

CREATE TABLE insert_ignore (
    id INT PRIMARY KEY,
    log_time TIME
);
INSERT INTO insert_ignore VALUES (1, '14:44:12');

方案一：RULE 規則 (效能最好、語法簡潔) ✅ 推薦

CREATE RULE r_insert_ignore AS
ON INSERT TO insert_ignore
WHERE EXISTS (SELECT 1 FROM insert_ignore WHERE id = NEW.id)
DO INSTEAD NOTHING;

測試：

INSERT INTO insert_ignore VALUES (1, CURRENT_TIME), (2, CURRENT_TIME);
-- 結果: INSERT 0 1  (id=1 被忽略，id=2 寫入)
SELECT * FROM insert_ignore;

• ✅ 內核層面攔截，零額外函數呼叫開銷。
• ✅ 批次 INSERT 單條語句即可處理多行忽略。
• ⚠️ RULE 為 PostgreSQL 專有，遷移性差；複雜條件下除錯較難。

方案二：BEFORE INSERT Trigger + 返回 NULL

-- 1. 函數
CREATE OR REPLACE FUNCTION sp_insert_ignore()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    PERFORM 1 FROM insert_ignore WHERE id = NEW.id;
    IF FOUND THEN
        RETURN NULL;  -- 忽略此行
    END IF;
    RETURN NEW;       -- 正常插入
END;
$$;

-- 2. 觸發器
CREATE TRIGGER tr_ib_insert_ignore
BEFORE INSERT ON insert_ignore
FOR EACH ROW EXECUTE PROCEDURE sp_insert_ignore();

• ✅ 標準 SQL 觸發器機制，可移植性較好。
• ✅ 邏輯靈活，可加入複雜判斷。
• ⚠️ **逐行執行**，大批次 INSERT 效能較 RULE 差。

方案三：CTE (WITH) + NOT IN / NOT EXISTS (單條語句、無物件)

WITH ytt_test(f1, f2) AS (
    VALUES (6, CURRENT_TIME), (3, CURRENT_TIME)
)
INSERT INTO insert_ignore (id, log_time)
SELECT a.f1, a.f2
FROM ytt_test a
WHERE a.f1 NOT IN (SELECT id FROM insert_ignore);

• ✅ 無需建立額外物件，適合臨時/一次性腳本。
• ✅ 可直接用於應用程式 SQL。
• ⚠️ 並發下存在 **Race Condition** (檢查到插入間隙可能被其他事務插入相同鍵)，**不適合高併發寫入**。

方案四：PL/pgSQL Function 捕獲 unique_violation 例外

CREATE OR REPLACE FUNCTION sp_insert_ignore(f_id INT, f_log_time TIME)
RETURNS VOID LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO insert_ignore VALUES (f_id, f_log_time);
EXCEPTION WHEN unique_violation THEN
    RAISE NOTICE 'Duplicated Key Error on ID:%', f_id;
    RETURN;
END;
$$;

呼叫：SELECT sp_insert_ignore(1, '14:22:35');

• ✅ 邏輯封裝在函數，應用層簡單呼叫。
• ✅ 可自定義錯誤處理 (記錄日誌、計數等)。
• ⚠️ **逐行呼叫**，批次需迴圈，效能最差；例外處理有額外開銷。

PostgreSQL 15+ 現代寫法 (標準化)

INSERT INTO insert_ignore (id, log_time)
VALUES (1, '14:44:12'), (2, '14:48:22')
ON CONFLICT (id) DO NOTHING;

• ✅ **SQL 標準** (ON CONFLICT 為 SQL:2003 MERGE 子集)。
• ✅ 效能最佳、語法最清晰、支援 DO UPDATE SET ... (UPSERT)。
• 👉 **新專案請直接使用此語法**，無需上述 workaround。

方案選擇指南

理解各方案權衡，依版本與場景選擇，即可在 PostgreSQL 优雅實現 INSERT IGNORE 語義。