PostgreSQL에서 Bulk Upsert

PostgreSQL에서 Bulk Upsert

PostgreSQL 9.5에서 ON CONFLICT 가 도입된 후 Upsert (Insert or Update) 문을 쓸수 있게 되었지만、복수행을 bulk로 보내는건 불가했다.

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)

주의점

select문과 update, insert 문과의 차이점은 select문은 복수행을 한번에 취득한다는 점이고, update, insert 문은 row by row로 결과를 취득한다는 것이다. 때문에 위의 UPDATE문에서 n테이블을 사용했을 때, 마치 for loop 도는 것과 같은 액션이 가능한 것