PostgreSQL 務實應用（二/5）插入衝突

在項目中，有時會動態地按周期（如按月）封存統計數據，通常需要做這樣的處理：

以按月封存為例，當月數據到達時，先需要檢查該月是否有過記錄，有則以更新的方式累加統計數字，無則添加一條記錄。

假設我們創建以下月封存表 month_stat，欄位 month_name 表示月份，欄位 total_count 表示統計數字。

CREATE TABLE month_stat (month_name varchar(6), total_count int, UNIQUE (month_name));

普通處理

假設數據到達，我們用 v_month_name 表示到達數據的月份，v_count 表示本次到達的數量，則通常我們使用以下方式完成月封存數據的記錄：

DO $$ 
DECLARE
   v_month_name varchar := '201904'; -- 本次數據的月份
   v_count int          := 3;        -- 本次數據相關的數量
BEGIN
   -- 如果月份已經存在，則更新統計，將數量累加上去
   IF EXISTS (SELECT 1 FROM month_stat WHERE month_name = v_month_name FOR UPDATE) THEN
      UPDATE month_stat set total_count = total_count + v_count 
       WHERE month_name = v_month_name;
   ELSE 
   -- 插入月份，數量為本次數量
      INSERT INTO month_stat (month_name, total_count) VALUES (v_month_name, v_count);
   END IF;
END $$;

判斷邏輯在 BEGIN 與 END 之間，先判斷月份是否存在，再按分支進行更新或插入處理。

使用 ON CONFLECT

好消息是，從 postgres-9.5 起增加了插入衝突的支持：

INSERT … ON CONFLICT DO NOTHING/UPDATE

於是有了以下寫法：

INSERT INTO month_stat (month_name, total_count) 
VALUES ('201904', 3)
ON CONFLICT(month_name)
DO 
  UPDATE set total_count = month_stat.total_count + EXCLUDED.total_count;

CONFLICT 後邊括弧中必須是建立了唯一索引（或主鍵）的欄位或欄位集。

DO 後邊可以是 NOTHING 表示衝突時忽略，什麼都不做。也可以是 UPDATE，表示衝突時需要更新，本次衝突相關的數據使用 EXCLUDED 來引用。

使用 ON CONFILECT 至少有兩個好處：

• 不需要自已費心去加事務鎖，因為它就是一個語句
• 代碼簡潔無分支結構

至於使用 NOTHING 還是 UPDATE，以及 UPDATE 更新的內容與條件則要根據業務規則（如值變化時才更新，或存在則不更新等）具體分析。