PostgreSQL UPSERT, ON CONFLICT 사용법과 자주 나는 에러 2가지

[실무 경험 한 줄: 예) 배치에서 "있으면 수정, 없으면 입력"을 SELECT 후 분기로 짰다가 동시 실행 때 중복 에러가 났던 상황]

"있으면 수정하고 없으면 입력한다." 오라클에서는 MERGE INTO로 하던 작업을 PostgreSQL에서는 보통 INSERT ... ON CONFLICT로 한다. 문법은 짧지만, 처음 쓰면 유니크 인덱스가 없다는 에러와 같은 행을 두 번 수정할 수 없다는 에러를 거의 반드시 만난다. 기본 사용법과 두 에러의 원인, 해결 방법을 정리했다.

한 줄 요약
ON CONFLICT (키) DO NOTHING은 중복이면 건너뛰고, DO UPDATE SET 컬럼 = EXCLUDED.컬럼은 중복이면 새 값으로 바꾼다. 충돌 기준 컬럼에는 반드시 유니크 인덱스나 제약이 있어야 한다.

• 기준: PostgreSQL 16 이상 (18에서 추가된 기능은 따로 표시)

예제 테이블

CREATE TABLE product (
    product_code varchar(20) PRIMARY KEY,
    name         text        NOT NULL,
    price        integer     NOT NULL,
    updated_at   timestamp   NOT NULL DEFAULT now()
);

INSERT INTO product (product_code, name, price) VALUES ('P001', '키보드', 30000);

1. DO NOTHING: 중복이면 건너뛰기

INSERT INTO product (product_code, name, price)
VALUES ('P001', '키보드', 32000)
ON CONFLICT (product_code) DO NOTHING;
-- INSERT 0 0  (이미 있으니 아무것도 안 함)

로그나 이벤트를 넣을 때 "이미 받은 건 무시"하는 용도로 많이 쓴다. DO NOTHING은 충돌 기준을 생략할 수도 있다. 생략하면 테이블의 모든 유니크 제약 충돌을 건너뛴다.

2. DO UPDATE: 중복이면 새 값으로 바꾸기

INSERT INTO product (product_code, name, price)
VALUES ('P001', '키보드', 32000)
ON CONFLICT (product_code) DO UPDATE
SET name       = EXCLUDED.name,
    price      = EXCLUDED.price,
    updated_at = now();

EXCLUDED는 넣으려다 충돌한 새 행을 가리키는 특별한 이름이다. 기존 행은 테이블 이름(product.price)으로 부른다. DO UPDATE는 충돌 기준을 꼭 적어야 한다.

3. 값이 바뀐 경우에만 수정하기

가격이 그대로인데도 매번 UPDATE가 일어나면 updated_at이 의미 없이 바뀌고, 불필요한 쓰기(데드 튜플)가 쌓인다. WHERE로 조건을 건다.

INSERT INTO product AS p (product_code, name, price)
VALUES ('P001', '키보드', 32000)
ON CONFLICT (product_code) DO UPDATE
SET price      = EXCLUDED.price,
    updated_at = now()
WHERE p.price IS DISTINCT FROM EXCLUDED.price;

IS DISTINCT FROM은 NULL까지 안전하게 비교한다. 조건이 거짓이면 수정하지 않지만, 해당 행에 잠금은 걸린다는 점은 알아 두자.

4. 여러 건을 한 번에

INSERT INTO product (product_code, name, price)
VALUES ('P001', '키보드', 32000),
       ('P002', '마우스', 15000),
       ('P003', '모니터', 210000)
ON CONFLICT (product_code) DO UPDATE
SET name = EXCLUDED.name, price = EXCLUDED.price, updated_at = now();

다른 테이블에서 옮겨 올 때는 INSERT INTO product SELECT ... FROM staging ON CONFLICT ...처럼 쓴다.

5. 입력됐는지 수정됐는지 알고 싶을 때

RETURNING으로 처리된 행을 돌려받을 수 있다. PostgreSQL 18부터는 수정 전 값(old)과 수정 후 값(new)을 함께 받을 수 있다.

-- PostgreSQL 18 이상
INSERT INTO product (product_code, name, price)
VALUES ('P002', '마우스', 17000)
ON CONFLICT (product_code) DO UPDATE SET price = EXCLUDED.price
RETURNING old.price AS before_price, new.price AS after_price;
-- 새로 입력된 행이면 before_price가 NULL

17 이하에서는 RETURNING (xmax = 0) AS inserted로 입력과 수정을 구분하는 방법이 많이 쓰인다. 내부 시스템 컬럼을 이용하는 방법이라 공식적으로 보장된 동작은 아니다. [실행 확인 필요]

자주 나는 에러 1: 유니크 제약이 없다

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

원인: ON CONFLICT (컬럼)에 적은 컬럼 조합에 맞는 유니크 인덱스나 제약이 없다. PostgreSQL은 이 인덱스로 충돌을 판단하기 때문에, 인덱스가 없으면 아예 실행하지 않는다.

해결: 충돌 기준 컬럼에 유니크 인덱스를 만든다. 여러 컬럼이면 순서는 상관없지만 조합이 정확히 같아야 한다.

CREATE UNIQUE INDEX ux_sales_daily ON sales_daily (shop_id, sale_date);

INSERT INTO sales_daily (shop_id, sale_date, amount) VALUES (10, '2026-10-20', 50000)
ON CONFLICT (shop_id, sale_date) DO UPDATE SET amount = EXCLUDED.amount;

자주 나는 에러 2: 같은 행을 두 번 수정할 수 없다

ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

원인: 한 번의 INSERT 안에 같은 키가 두 번 들어 있다. 첫 번째 행으로 수정한 행을 두 번째 행이 또 수정하려 하면, 어느 값이 최종인지 정할 수 없어 에러를 낸다. 스테이징 테이블에 같은 상품이 여러 번 들어온 경우에 자주 난다.

해결: 넣기 전에 키별로 한 건만 남긴다. DISTINCT ON으로 가장 최근 행만 고른다.

INSERT INTO product (product_code, name, price)
SELECT DISTINCT ON (product_code) product_code, name, price
FROM   staging_product
ORDER  BY product_code, received_at DESC
ON CONFLICT (product_code) DO UPDATE
SET name = EXCLUDED.name, price = EXCLUDED.price, updated_at = now();

알아두면 좋은 점

주의

• 시퀀스 번호가 비는 건 정상이다. IDENTITY나 serial 컬럼이 있으면 충돌로 입력되지 않은 행도 번호를 하나씩 쓴다. 번호가 1, 2, 5, 9처럼 띄엄띄엄해도 버그가 아니다.
• 충돌 기준은 ON CONSTRAINT 제약이름으로도 쓸 수 있지만, 공식 문서는 컬럼으로 적는 방식(인덱스 추론)을 권한다. 인덱스를 다시 만들어도 쿼리를 고칠 필요가 없기 때문이다.
• 파티션 테이블에서 DO UPDATE로 파티션 키 값을 바꿔 다른 파티션으로 옮기는 것은 지원하지 않는다.

오라클 MERGE, PostgreSQL MERGE와 비교

PostgreSQL도 15부터 MERGE를 지원한다. 그럼 무엇을 써야 할까?

항목	INSERT ... ON CONFLICT	MERGE (PostgreSQL 15 이상)
동시 실행	입력 또는 수정 중 하나가 원자적으로 보장	동시에 같은 키가 들어오면 유니크 위반 에러가 날 수 있다
삭제 처리	안 됨	WHEN MATCHED THEN DELETE 가능
유니크 인덱스	필수	필요 없음 (조인 조건으로 판단)
오라클 문법과 비슷한 정도	다름	오라클 MERGE INTO와 비슷

단순한 "있으면 수정, 없으면 입력"은 ON CONFLICT, 삭제까지 섞인 동기화 작업은 MERGE가 맞다. 동시에 여러 세션이 같은 키를 넣는 환경이라면 ON CONFLICT가 안전하다. [실행 확인 필요: MERGE 동시 실행 시 동작]

정리

1. 충돌 기준 컬럼에 유니크 인덱스가 있는지 먼저 확인한다.
2. 무시할지(DO NOTHING) 바꿀지(DO UPDATE) 정한다. 바꿀 때는 EXCLUDED로 새 값을 쓴다.
3. 여러 건을 넣을 때는 같은 키가 두 번 들어 있지 않게 DISTINCT ON으로 정리한다.

참고 문서

• PostgreSQL 공식 문서, INSERT (ON CONFLICT 절): https://www.postgresql.org/docs/current/sql-insert.html
• PostgreSQL 공식 문서, MERGE: https://www.postgresql.org/docs/current/sql-merge.html

함께 보면 좋은 글

• 오라클 개발자가 PostgreSQL에서 처음 헷갈리는 10가지 ([예정 링크] 011번 글 발행 후 주소 넣기)
• [예정 글] PostgreSQL SERIAL vs IDENTITY, 시퀀스 값이 꼬였을 때 맞추는 법
• 지운 데이터가 왜 용량을 차지할까, PostgreSQL VACUUM과 테이블 블로트 (기존 글: https://fireyolo.tistory.com/23)