【postgresql 基礎入門】UPSERT語句，INSERT違反約束條件時可以轉變爲UPDATE語句，UPDATE與INSERT的合體

PostgreSQL 是一個功能強大的開源關係資料庫系統（RDBMS），它提供了許多進階特性來幫助開發人員和管理員處理資料庫操作。其中一個重要的特性就是 UPSERT 語句，這是一種結合了 INSERT 和 UPDATE 的語法，用於在資料庫中插入新行或更新現有的匹配行。當 INSERT 違反某個表中的完整性約束時，UPSERT 會自動轉換為 UPDATE 語句，從而避免了可能出現的錯誤並簡化了資料庫程序設計的工作量。以下將詳細介紹 PostgreSQL 中 UPSERT 語句的使用方式以及其背後的運作原理。

什麼是 UPSERT？

UPSERT 是英文「Insert Update」的縮寫，它允許用戶在一次單一的 SQL 查詢中執行兩個動作：如果目標表中不存在符合條件的主鍵值或唯一索引值的資料行，則執行 INSERT；反之，若存在相符的資料行，則執行 UPDATE 以更新這些資料行的值。這種語法有助於減少需要撰寫的程式碼量，同時也提高了資料庫應用的效能與可維護性。

PostgreSQL 中的 UPSERT 語句格式

在 PostgreSQL 中，UPSERT 語句的基礎格式如下所示：

-- 如果存在就更新，否則插入
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...) ON CONFLICT DO UPDATE SET column1 = EXCLUDED.column1, column2 = EXCLUDED.column2;

上述語句中，`ON CONFLICT` 子句告訴 PostgreSQL 在遇到衝突時應該如何處理，在本例中，我們設置 `DO UPDATE` 來指示系統嘗試進行更新操作。此外，`EXCLUDED` 表達式可用來訪問那些因為衝突而被排除的新建資料行的值。這樣做可以使您在更新已存在的資料行時能夠參考到即將被插入的資料。

使用 UPSERT 語句的範例

下面提供了一個簡單的例子來說明如何在實際應用中使用 UPSERT 語句：

CREATE TABLE employees (
id INT PRIMARY KEY,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL
);

-- 假設 id=3 已經存在，那麼 INSERT 就會失敗
BEGIN; -- 開始事務
INSERT INTO employees (id, first_name, last_name, email) VALUES (3, 'John', 'Doe', '<EMAIL>');

-- 如果上一條指令失敗，則執行下列更新指令
COMMIT; -- 事務提交

在上面的例子中，由於 `id` 是主鍵且 `email` 有獨特性限制，因此 id=3 已經存在的情況下，直接執行 INSERT 會導致錯誤。然而，如果你想要確保無論是否發生衝突都能夠正確地對員工信息進行更新或新增，你可以使用 UPSERT 語句來達到這個目的：

-- UPSERT 語句版本
START TRANSACTION; -- 開始事務
INSERT INTO employees (id, first_name, last_name, email) VALUES (3, 'John', 'Doe', '<EMAIL>') ON CONFLICT(id) DO UPDATE SET first_name=excluded.first_name, last_name=excluded.last_name, email=excluded.email;
COMMIT; -- 事務提交

在上述 UPSERT 語句中，我們使用了 `ON CONFLICT(id)` 部分來指定只在 `id` 欄位上發生衝突時才觸發更新操作。此外，`SET first_name=excluded.first_name, last_name=excluded.last_name, email=excluded.email` 部分則是定義了哪些欄位的值將會從 INSERT 語句變更為新的值。

總結而言，UPSERT 語句不僅能夠提高資料庫應用程式的靈活性和效率，還能夠避免因重複資料輸入所引起的潛在問題。對於任何需要在資料庫中經常執行插入和更新的情況來說，UPSERT 都是一種極具吸引力的解決方案。

UPSERT 語句的其他用法

除了基本的 ON CONFLICT DO UPDATE 之外，PostgreSQL 還支援更多的 UPSERT 語句選項，例如：

• `ON CONFLICT IGNORE`: 忽略所有衝突而不執行任何操作。

• `ON CONFLICT DO NOTHING`: 不執行任何操作，即使沒有衝突也不會插入或更新資料。

• `ON CONFLICT (a, b, c) DO UPDATE …`: 你可以在多個列上定義衝突檢測規則。

注意事項

在使用 UPSERT 語句時，請注意以下幾點：

• UPSERT 語句只適用於 PostgreSQL 9.5 及更高版本的資料庫。

• UPSERT 語句不能用於外鍵關係的資料表。

• 在某些特定情境下，UPSERT 語句可能不如使用傳統的事務控制（如保存點）那麼靈活。

綜上所述，UPSERT 語句是 PostgreSQL 的一個強大特徵，它結合了 INSERT 和 UPDATE 的優勢，使得資料庫操作更加高效和便捷。透過本篇文章的說明，希望讀者能夠理解 UPSERT 語句的基本概念和使用方法，並且能在自己的專案中有效地運用這一特性。