摘要：什么是簡而言之，就是，不存在就插入，存在就更新。當然也有功能，和的類似。然而，命令不支持，這使一些增量的工作非常不方便。五把表清空上面過程確實很麻煩，如果使用的話，只需要簡單的相關鏈接

"UPSERT" is a DBMS feature that allows a DML statement"s author to atomically either insert a row, or on the basis of the row already existing, UPDATE that existing row instead, while safely giving little to no further thought to concurrency. One of those two outcomes must be guaranteed, regardless of concurrent activity, which has been called "the essential property of UPSERT".

簡而言之，就是，不存在就插入，存在就更新。

MySQL有INSERT...ON DUPLICATE KEY UPDATE語法，可以實現Upsert：

INSERT INTO customers (id, first_name, last_name, email) VALUES (30797, "hooopo1", "wang", "hoooopo@gmail.com") 
ON DUPLICATE KEY UPDATE 
first_name = VALUES(first_name), last_name = VALUES(last_name);

PostgreSQL 從 9.5 也有了INSERT ... ON CONFLICT UPDATE語法，效果和 MySQL 類似：

INSERT INTO customers (id, first_name, last_name, email) VALUES (30797, "hooopo1", "wang", "hoooopo@gmail.com") 
ON CONFLICT(id) DO  UPDATE 
SET first_name = EXCLUDED.first_name, last_name = EXCLUDED.last_name;

之前研究 MySQL 里如何插入最快 ，里面提到 LOAD INFILE 方式批量插入，并且 MySQL 的 bulk insert 是支持 REPLACE 語意的，即批量插入的同時還可以 upsert。

LOAD DATA LOCAL INFILE "/Users/hooopo/data/out/product_sales_facts.txt"
REPLACE INTO TABLE product_sale_facts FIELDS TERMINATED BY "," (`id`,`date_id`,`order_id`,`product_id`,`address_id`,`unit_price`,`purchase_price`,`gross_profit`,`quantity`,`channel_id`,`gift`)

當然 PostgreSQL 也有 Copy功能，和 MySQL 的 LOAD INFILE 類似。然而，copy 命令不支持 Upsert，這使一些增量 ETL 的工作非常不方便。

不過有一種利用 staging 表的方式實現 bulk upsert，大致步驟如下：

一. 目標表

二. 把增量數據批量插入中間表

CREATE TABLE IF NOT EXISTS staging  LIKE customers INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES;
COPY staging (id, email, first_name, last_name)
        FROM STDIN
          WITH
            DELIMITER ","
            NULL "N"
            CSV;

三. 把目標表中與 staging 表沖突部分刪掉

DELETE FROM customers
USING staging
WHERE customers.id = staging.id

四. 把 staging 表批量插入到目標表，因為沖突部分已經刪掉，所以這步不會有任何沖突。

INSERT INTO customers (SELECT * FROM staging);

五. 把 staging 表清空

TRUNCATE TABLE staging;

上面過程確實很麻煩，如果使用 kiba-plus 的話，只需要簡單的 DSL：

destination Kiba::Plus::Destination::PgBulk2, { :connect_url => DEST_URL,
                                :table_name => "customers",
                                :truncate => false,
                                :columns => [:id, :email, :first_name, :last_name],
                                :incremental => true,
                                :unique_by => :id
                              }

相關鏈接：

https://wiki.postgresql.org/w...

http://www.silota.com/blog/am...

https://hashrocket.com/blog/p...

http://docs.aws.amazon.com/re...

http://stackoverflow.com/ques...