upsert

PostgreSQL 9.5 釋出，UPSERT！

PostgreSQL 9.5 正式發行，這次新增了大家期待已久的 UPSERT 功能：「PostgreSQL 9.5: UPSERT, Row Level Security, and Big Data」。

SQL:2003 正式定義出 UPSERT，被稱為 Merge，不過看網路上一般還是比較習慣 UPSERT 這個用法：

A relational database management system uses SQL MERGE (also called upsert) statements to INSERT new records or UPDATE existing records depending on whether condition matches.

也就是當沒資料的時候就 INSERT，有資料的時候就 UPDATE 的語法。常見的使用情境是拿來當 counter 用 (雖然這很傷資料庫的效能)。

沒有 UPSERT 的時候只能用 transaction 或是 store procedure 搭出來，效能上會比在 database engine 裡實作來的差，所以 UPSERT 還是被實作出來了。

PostgreSQL 9.5 的 UPSERT

在「Upsert Lands in PostgreSQL 9.5 – a First Look」這邊提到了 PostgreSQL 9.5 支援的 UPSERT 操作。

UPSERT 的定義是：

(computing, database) An operation that inserts rows into a database table if they do not already exist, or updates them if they do.

如果不存在就 INSERT，如果存在就 UPDATE，然後取一部分的字變成 UPSERT。由於要偵測「存在」，只能用在有 primary key 或是有 unique 條件時的表格上。

作者給的範例講解了 PostgreSQL 9.5 上的語法：

INSERT INTO products (
    upc,
    title,
    description,
    link)
VALUES (
    123456789,
    ‘Figment #1 of 5’,
    ‘THE NEXT DISNEY ADVENTURE IS HERE - STARRING ONE OF DISNEY'S MOST POPULAR CHARACTERS! ’,
    ‘http://www.amazon.com/dp/B00KGJVRNE?tag=mypred-20'
    )
ON CONFLICT DO UPDATE SET description=excluded.description;

這邊用的是 ON CONLIFCT DO UPDATE SET，在 MySQL 則是用 ON DUPLICATE UPDATE。

UPSERT

維基百科對 UPSERT 的說明：(取自「Merge (SQL)」條目)

A relational database management system uses SQL MERGE (also called upsert) statements to INSERT new records or UPDATE existing records depending on whether or not a condition matches.

在 MySQL 裡的兩種語法其實就是在實做這個需求：

REPLACE INTO ...
INSERT INTO ... ON DUPLICATE KEY UPDATE ...

而前者其實是後者的一個特例 (當 INSERT 發現有 dupe key 時把現有的 record 改成與 INSERT 時相同的條件)。

而計數器是後者常見的 case 之一：當 record 不存在的時候塞一筆進去，並且將 counter 設為 1；當 record 存在的時候對 counter 加一更新。像是這樣的 SQL query：

INSERT INTO my_table SET id = ?, num = 1 ON DUPLICATE KEY UPDATE num = num + 1;

由於這是常見的需求，使得這個語法是目前少數 MySQL 比 PostgreSQL 好用的地方。

在「A Case for Upserts」這篇就看到抱怨 PostgreSQL 不實做這個功能...

不過我覺得作者寫得有點誇張，INSERT INTO ... ON DUPLICATE KEY UPDATE ... 應該是可以模擬出來的功能：當 INSERT 失敗後再跑 UPDATE。而 REPLACE INTO ... 是特例，也就當然可以模擬出來。