Posted at

PostgreSQL で Bulk Upsert

PostgreSQL 9.5 から ON CONFLICT が導入されて Upsert (Insert or Update) ができるようになったものの、複数行まとめてやることはできなかった。

ON CONFLICT を使わなくても、CTE を使うことで以下のように Bulk Upsert することができるっぽい。

WITH

-- write the new values
n(ip,visits,clicks) AS (
VALUES ('192.168.1.1',2,12),
('192.168.1.2',6,18),
('192.168.1.3',3,4)
),
-- update existing rows
upsert AS (
UPDATE page_views o
SET visits=n.visits, clicks=n.clicks
FROM n WHERE o.ip = n.ip
RETURNING o.ip
)
-- insert missing rows
INSERT INTO page_views (ip,visits,clicks)
SELECT n.ip, n.visits, n.clicks FROM n
WHERE n.ip NOT IN (
SELECT ip FROM upsert
)

※ SQL は以下のページからの引用

Faster data updates with CartoDB — CARTO Blog