// PostgreSQL 錯誤參考

PostgreSQL 23505:重複鍵值違反唯一條件約束

INSERT 或 UPDATE 會在受主鍵、唯一約束或唯一索引保護的欄位中,產生第二筆相同值的資料列。

SQLSTATE
23505
名稱
unique_violation
類別
完整性約束違規
錯誤訊息
duplicate key value violates unique constraint "users_email_key"
DETAIL:  Key (email)=([email protected]) already exists.

它的意義

PostgreSQL 強制執行每個主鍵, UNIQUE 約束與唯一索引會在資料列寫入的當下就檢查。如果新寫入或修改的資料列,其鍵值與既有資料列相同(或與另一個交易已寫入但尚未提交的資料列相同),該陳述式就會以 23505 失敗,且不會保留任何變更。

這 DETAIL 行會指出欄位和重複的值,訊息則會指出條件約束或索引,讓你確切知道違反了哪一條規則。

常見原因

  • 插入索引鍵已存在的列,例如重新執行匯入或 seed 腳本。
  • A serial 或識別序列落後於資料,通常發生在以明確 id 載入資料列之後。下一個預設 id 會撞上現有的資料列:經典的 Key (id)=(1) already exists.
  • 兩個工作階段同時插入同一個鍵;第二個會等第一個,第一個一提交,第二個就立刻失敗。
  • 一個 UPDATE 把唯一欄位設成另一列已有的值。

如何修正

  1. 找出現有的資料列,名稱記載於 DETAIL 逐列檢查,判斷新資料是重複資料還是不同的記錄。

    SELECT * FROM users WHERE email = '[email protected]';
  2. 如果寫入應該改為更新現有的列,請將其改為 upsert,並搭配 ON CONFLICT.

    INSERT INTO users (email, name)
    VALUES ('[email protected]', 'Ada')
    ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;
  3. 如果應該簡單地跳過重複項,請使用 ON CONFLICT DO NOTHING.

    INSERT INTO users (email, name)
    VALUES ('[email protected]', 'Ada')
    ON CONFLICT (email) DO NOTHING;
  4. 如果重複發生在某個 serial 或識別欄位的 ID,將序列移到現有最大值之後。

    SELECT setval(pg_get_serial_sequence('users', 'id'),
                  (SELECT COALESCE(MAX(id), 0) + 1 FROM users), false);

安全地重現問題

CREATE TEMP TABLE t (id int PRIMARY KEY);
INSERT INTO t VALUES (1);
INSERT INTO t VALUES (1);  -- ERROR: duplicate key value violates unique constraint "t_pkey"

問題

我從沒設定過 id,為什麼插入還是因為 id 欄位重複鍵而失敗?

資料表的序列落後於資料,通常是因為插入資料列時指定了明確的 id,或是還原時未更新序列值。可以使用下列指令重設: setval 到再往後一格 MAX(id).

ON CONFLICT 是否對任何唯一索引有效?

它適用於唯一索引或約束的欄位,或搭配 ON CONFLICT ON CONSTRAINT name。它需要 PostgreSQL 9.5 或更新版本。

相關錯誤

更多完整性條件約束違規錯誤

程式碼和訊息文字來自 PostgreSQL 文件,附錄 A:PostgreSQL 錯誤碼。瀏覽完整的 PostgreSQL 錯誤清單 或 搜尋每個資料庫錯誤.

// 錯誤更少,修復更快

在 SQLly 裡操作 PostgreSQL

適用於 macOS、Windows 和 Linux 的原生 SQL 客戶端,具備結構描述感知的 IntelliSense、高風險陳述式防護與清楚的錯誤訊息,適用於 PostgreSQL 和你使用的其他資料庫。