PostgreSQL upsert(插入更新)數據的操作詳解

Posted on by

本文介紹如何使用PostgreSQL upsert特性插入或當被插入數據已存在則更新數據。

1. 介紹PostgreSQL upsert

在關系型數據庫中，upsert是一個組合詞，即當往表中插入記錄，如果該記錄已存在則更新，否則插入新記錄。為瞭使用該特性需要使用INSERT ON CONFLICT語句：

INSERT INTO table_name(column_list) 
VALUES(value_list)
ON CONFLICT target action;

該語法中target可以是下面列舉內容之一：

  • (column_name) – 列名
  • ON CONSTRAINT constraint_name – 唯一約束的名稱
  • WHERE predicate – 帶謂詞的where子句.

action可能為下面兩者之一：

DO NOTHING – 如果行已存在表中，不執行任何動作.
DO UPDATE SET column_1 = value_1, … WHERE condition – 更新表中一些字段.

註意：ON CONFLICT子句僅從PostgreSQL 9.5版本才有效。如果需用在之前版本，需要使用其他方法實現。

2. PostgreSQL upsert示例

下面語句創建customers表，演示PostgreSQL upsert特性：

DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
	customer_id serial PRIMARY KEY,
	name VARCHAR UNIQUE,
	email VARCHAR NOT NULL,
	active bool NOT NULL DEFAULT TRUE
);

customers 表包括四個字段customer_id, name, email, active，name字段有唯一約束確保名稱唯一。

下面插入語句新增幾條記錄：

INSERT INTO 
 customers (name, email)
VALUES 
 ('IBM', '[email protected]'),
 ('Microsoft', '[email protected]'),
 ('Intel', '[email protected]');

假設現在Microsoft 修改email字段，從 [email protected] 到 [email protected]。我們可以使用update更新語句，因為需要演示upsert特性，這裡使用INSERT ON CONFLICT語句：

INSERT INTO customers (NAME, email)
VALUES('Microsoft','[email protected]') 
ON CONFLICT ON CONSTRAINT customers_name_key 
DO NOTHING;

上面語句表示如果名稱表中存在，則什麼都不做。下面語句與上面等價，但使用name列代替唯一約束名稱：

INSERT INTO customers (name, email)
VALUES('Microsoft','[email protected]') 
ON CONFLICT (name) 
DO NOTHING;

假設當記錄已存在時你需要連接新的郵箱至原郵箱，這時update動作：

INSERT INTO customers (name, email)
VALUES('Microsoft','[email protected]') 
ON CONFLICT (name) 
DO 
 UPDATE SET email = EXCLUDED.email || ';' || customers.email;

這裡使用EXCLUDED虛擬表，其包含我們要更新的記錄，也就是新記錄（相對於原記錄customers）。等式右邊字段需要表名進行區分，才不會報字段不明確錯誤。
讀者可以自行驗證結果是否如你所願。

3. 總結

本文介紹通過INSERT ON CONFLICT實現PostgreSQL插入更新特性。

到此這篇關於PostgreSQL upsert(插入更新)教程詳解的文章就介紹到這瞭,更多相關PostgreSQL upsert內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：