PostgreSQL Upsert用法

PostgreSQL使用Upsert新增或修改(upsert)的方式如下。

Upsert是INSERT與UPDATE的結合，當資料不存在執行新增，反之執行修改，只需一次交易即可完成，保證Insert與Update的原子性(atomicity)操作。

沒有Upsert時需先以SELECT查詢資料是否存在，否則新增，是則修改，會有兩次交易。兩次交易缺點是效能較差，且併行交易(concurrent transaction)會有資料不一致的問題。

        ┌────────┐
        │ SELECT ├────────────────────┐
        └────┬───┘                    │
             │                        t1
             ▼                        │
    ┌─N──<exist?>──Y─┐                │
    │                │                ▼
    ▼                ▼             /──────\
┌────────┐      ┌────────┐        /   DB   \
│ INSERT │      │ UPDATE │        \        /
└───┬────┘      └────┬───┘        │\──────/│
    │                │            \        /
    └────────────────┴────t2─────► \──────/

PostgreSQL的Upsert是以INSERT ... ON CONFLICT ... DO UPDATE語法來實現。

例如現有空資料表如下：

employee

CREATE TABLE IF NOT EXISTS employee (
   id bigserial PRIMARY KEY,
   name varchar(60) UNIQUE NOT NULL,
   age integer,
   created_at timestamp NOT NULL
);

使用Upsert語法一筆資料。當INSERT時若與ON CONFLICT欄位name的值衝突時執行UPDATE。

INSERT INTO employee (name, age, created_at)
VALUES ('john', 33, now())
ON CONFLICT (name) 
DO UPDATE SET age = 34;

psql執行。

postgres=> INSERT INTO employee (name, age, created_at)
VALUES ('john', 33, now())
ON CONFLICT (name)
DO UPDATE SET age = 34;
INSERT 0 1

查詢剛Upsert的資料，由於原本沒資料所以新增。

postgres=> SELECT * FROM employee;
 id | name | age |         created_at
----+------+-----+----------------------------
  1 | john |  33 | 2022-03-20 08:49:28.509023
(1 row)

再次執行同樣的Upsert，因為name的值與ON CONFLICT的條件衝突所以變成修改。第二次Upsert後的結果如下。

postgres=> SELECT * FROM employee;
 id | name | age |         created_at
----+------+-----+----------------------------
  1 | john |  34 | 2022-03-20 08:49:28.509023
(1 row)

ON CONFLICT也可用ON CONSTRAINT加上限制名稱，例如employee.name有UNIQUE唯一限制employee_name_key。

執行Upsert如下：

INSERT INTO employee (name, age, created_at)
VALUES ('john', 33, now())
ON CONFLICT ON CONSTRAIT employee_name_key
DO UPDATE SET age = 35;

psql執行。

postgres=> INSERT INTO employee (name, age, created_at)
VALUES ('john', 33, now())
ON CONFLICT ON CONSTRAINT employee_name_key
DO UPDATE SET age = 35;
INSERT 0 1

查詢結果。

postgres=> SELECT * FROM employee;
 id | name | age |         created_at
----+------+-----+----------------------------
  1 | john |  35 | 2022-03-20 08:49:28.509023
(1 row)

• PostgreSQL Doc - ON CONFLICT Clause