[PostgreSQL] MERGE 심화 — ON CONFLICT로는 안 되는 DELETE 다분기와 cardinality violation

설치·접속: PostgreSQL 설치와 접속

부제: 밤마다 들어오는 재고 변동 파일을 반영하는데, 신규는 INSERT·증감 후 양수면 UPDATE·0 이하가 되면 그 행을 DELETE — 세 갈래를 한 문장으로 하고 싶을 때

대부분 upsert를 INSERT ... ON CONFLICT로만 안다. 하지만 ON CONFLICT는 "충돌 시 UPDATE"만 되고 DELETE나 "조건별 다른 액션"은 표현할 수 없다. MERGE는 소스 행마다 WHEN 절을 순서대로 평가해 처음 참이 되는 절 하나만 실행한다. 재고가 0 이하로 떨어지면 DELETE, 아니면 UPDATE 같은 다분기가 한 문장에 담긴다.

CREATE TABLE wines (winename text PRIMARY KEY, stock int);
CREATE TABLE wine_stock_changes (winename text, stock_delta int);

INSERT INTO wines VALUES ('Chardonnay', 5), ('Merlot', 2);
INSERT INTO wine_stock_changes VALUES
  ('Chardonnay', 3),   -- 기존 → UPDATE (5+3=8)
  ('Merlot', -2),      -- 기존 → 0 이하 → DELETE
  ('Riesling', 4);     -- 신규 → INSERT

MERGE INTO wines w
USING wine_stock_changes s ON s.winename = w.winename
WHEN NOT MATCHED AND s.stock_delta > 0 THEN
  INSERT VALUES(s.winename, s.stock_delta)
WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN
  UPDATE SET stock = w.stock + s.stock_delta
WHEN MATCHED THEN
  DELETE;

SELECT * FROM wines ORDER BY winename;

MERGE 3
  winename  | stock
------------+-------
 Chardonnay |     8
 Riesling   |     4

세 갈래가 한 문장에서 다 돌았다. Chardonnay는 매칭되고 5+3>0이라 UPDATE로 8, Merlot은 매칭되지만 2-2=0이라 위 UPDATE 절을 통과 못 하고 마지막 WHEN MATCHED THEN DELETE로 삭제, Riesling은 매칭 안 되고 delta가 양수라 INSERT. WHEN 절은 위에서 아래로 평가되고 candidate row당 첫 참인 절만 실행되므로 순서가 곧 우선순위다.

이걸 ON CONFLICT로는 못 짠다. ON CONFLICT는 "충돌 시 DELETE"라는 문법이 없다. 공식 Notes도 두 문을 "서로 교환 가능하지 않다(not interchangeable)"고 못 박는다. 동시성 안전망도 다르다. ON CONFLICT는 동시 INSERT 충돌을 UPDATE로 흡수하는 보호가 있지만, MERGE에는 그런 자동 재시도가 없다.

cardinality violation — target 한 행에 source 여러 행이 매칭되면

MERGE는 "각 target 행에 대해 candidate change 행이 최대 하나"임을 요구한다. 소스에 같은 키가 두 번 있어 한 target 행이 두 번 수정되려 하면 에러로 튕긴다.

INSERT INTO wine_stock_changes VALUES ('Chardonnay', 1);  -- Chardonnay 2행

MERGE INTO wines w
USING wine_stock_changes s ON s.winename = w.winename
WHEN MATCHED THEN UPDATE SET stock = w.stock + s.stock_delta
WHEN NOT MATCHED THEN INSERT VALUES (s.winename, s.stock_delta);

ERROR:  MERGE command cannot affect row a second time
HINT:  Ensure that not more than one source row matches any one target row.

과거 PG의 UPDATE ... FROM 조인은 중복 매칭 시 두 번째 이후를 조용히 무시했지만, MERGE는 명시적으로 에러를 던진다. 그래서 소스는 미리 키 단위로 집계(GROUP BY)하거나 DISTINCT ON으로 한 행만 남긴 뒤 MERGE에 넘겨야 한다. 이 엄격함이 "왜 값이 하나만 반영됐지" 같은 조용한 버그를 애초에 막아준다.

이렇게도 쓴다

소스를 미리 키 단위로 집계해 cardinality violation을 피한다.

MERGE INTO wines w
USING (SELECT winename, sum(stock_delta) AS d
       FROM wine_stock_changes GROUP BY winename) s
ON s.winename = w.winename
WHEN MATCHED THEN UPDATE SET stock = w.stock + s.d
WHEN NOT MATCHED THEN INSERT VALUES (s.winename, s.d);

 

변경 없는 행은 DO NOTHING으로 명시해 건너뛴다.

MERGE INTO t t USING s s ON t.k = s.k
WHEN MATCHED THEN DO NOTHING
WHEN NOT MATCHED THEN INSERT VALUES (s.k, s.v);

 

조건부 DELETE 분기로 "재고가 0 이하면 삭제, 아니면 갱신"을 순서로 표현한다.

MERGE INTO wines w
USING wine_stock_changes s ON s.winename = w.winename
WHEN MATCHED AND w.stock + s.stock_delta <= 0 THEN DELETE
WHEN MATCHED THEN UPDATE SET stock = w.stock + s.stock_delta
WHEN NOT MATCHED THEN INSERT VALUES (s.winename, s.stock_delta);

(WHEN NOT MATCHED BY SOURCE로 소스에 없는 target까지 지우는 전체 동기화는 PostgreSQL 17부터다. PG16에서는 위처럼 MATCHED 분기 조건으로 삭제를 표현한다.)

 

단순 upsert(충돌 시 UPDATE만)라면 굳이 MERGE 말고 ON CONFLICT가 더 짧고 동시성에 안전하다. (경계: 언제 ON CONFLICT로)

INSERT INTO wines VALUES ('Chardonnay', 8)
ON CONFLICT (winename) DO UPDATE SET stock = EXCLUDED.stock;

 

MERGE를 CTE·서브쿼리 소스와 결합해 여러 테이블을 조인한 결과를 반영한다. (조합: 서브쿼리 소스)

MERGE INTO wines w
USING (SELECT winename, stock_delta FROM wine_stock_changes
       WHERE stock_delta <> 0) s
ON s.winename = w.winename
WHEN MATCHED AND w.stock + s.stock_delta <= 0 THEN DELETE
WHEN MATCHED THEN UPDATE SET stock = w.stock + s.stock_delta;