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

Leave a Reply

Your email address will not be published. Required fields are marked *