紐約現代藝術博物館（Museum of Modern Art, MoMA）公開了館藏資料庫，每月更新。這份研究資料集包含 131,585 筆紀錄，涵蓋所有入藏並編目的作品：每件作品的基本 metadata（標題、藝術家、創作年代、媒材、尺寸、入藏日期），部分紀錄資訊不完整、標註為 “not Curator Approved”。
用 git 與 git lfs 可以取回近幾個月各版本的藝術家資料。逐月比較，大部分資料不變，少部分被更新。先載入五月版：
begin;
create schema if not exists moma;
create table moma.artist
(
constituentid integer not null primary key,
name text not null,
bio text,
nationality text,
gender text,
begin integer,
"end" integer,
wiki_qid text,
ulan text
);
\copy moma.artist from 'artists/artists.2017-05-01.csv' with csv header delimiter ','
commit;看看載入了什麼
select name, bio, nationality, gender
from moma.artist
limit 6; name │ bio │ nationality │ gender
═════════════════╪═════════════════════╪═════════════╪════════
Robert Arneson │ American, 1930–1992 │ American │ Male
Doroteo Arnaiz │ Spanish, born 1936 │ Spanish │ Male
Bill Arnold │ American, born 1941 │ American │ Male
...
(6 rows)更新資料#
五月資料載好後，收到六月的更新。這類更新的常態是：沒有 diff，只有一份內容全新的完整檔案。批次更新操作的典型實作是：
- 把新版資料從檔案載入一張 PostgreSQL 資料表或暫存表（temporary table）；
- 利用
update指令的 join 能力，以新值更新既有資料； - 利用
insert指令的 join 能力，把批次中的新資料插入目標表。
用 SQL 寫出來：
begin;
create temp table batch
(
like moma.artist
including all
)
on commit drop;
\copy batch from 'artists/artists.2017-06-01.csv' with csv header delimiter ','
with upd as
(
update moma.artist
set (name, bio, nationality, gender, begin, "end", wiki_qid, ulan)
= (batch.name, batch.bio, batch.nationality,
batch.gender, batch.begin, batch."end",
batch.wiki_qid, batch.ulan)
from batch
where batch.constituentid = artist.constituentid
and (artist.name, artist.bio, artist.nationality,
artist.gender, artist.begin, artist."end",
artist.wiki_qid, artist.ulan)
<> (batch.name, batch.bio, batch.nationality,
batch.gender, batch.begin, batch."end",
batch.wiki_qid, batch.ulan)
returning artist.constituentid
),
ins as
(
insert into moma.artist
select constituentid, name, bio, nationality,
gender, begin, "end", wiki_qid, ulan
from batch
where not exists
(
select 1
from moma.artist
where artist.constituentid = batch.constituentid
)
returning artist.constituentid
)
select (select count(*) from upd) as updates,
(select count(*) from ins) as inserts;
commit;這個實作忠實對應規格，幾個值得注意的技巧：
update與insert都用上了 join；returning子句順便輸出這次做了什麼的統計。- update 部分用列比較器（row comparator），只更新真正有變動的列。
- insert 部分用 anti-join（
not exists），只插入原本沒有的資料。
執行結果：
BEGIN
CREATE TABLE
COPY 15186
updates │ inserts
═════════╪═════════
35 │ 21
(1 row)
COMMIT這個腳本隱含了一個假設：MoMA 的
constituentid是這份資料集可靠的主鍵。部署到生產環境之前，這個假設當然應該先驗證。
並行模式#
雖然 update-or-insert 發生在單一查詢裡（單一資料庫快照、單一交易內），仍然無法防止被並行執行。棘手情況是應用程式同時跑了兩次上面的查詢：只要並行的兩份來源含有同一主鍵的資料，insert 就會撞出 duplicate key 錯誤——兩個交易看到的都是「新資料還不存在」的目標表，於是都決定要插入。
有兩種辦法避免：
確保同一時間只跑一個批次更新，把應用程式圍繞這個約束來設計。好方法是用手動的 lock 指令（見 PostgreSQL 的 explicit locking 文件）：
LOCK TABLE target IN SHARE ROW EXCLUSIVE MODE;這個鎖等級不會被任何 PostgreSQL 指令自動取得，所以它只在「你想序列化的每個交易都主動取鎖」時才有幫助；確定沒有風險（不在玩 insert-or-update 這套）時可以省略。
使用 PostgreSQL 9.5 新增的
insert … on conflict子句。
On Conflict Do Nothing#
PostgreSQL 9.5 起，可用 insert 的 on conflict 子句處理並行衝突。與前一版腳本的差異（diff）只有兩處：改讀七月的 CSV，以及在 insert 的 CTE 裡加上一行——
+ on conflict (constituentid) do nothing
returning artist.constituentid