UPSERT는 "있으면 UPDATE, 없으면 INSERT"를 한 문장으로 원자적으로 처리하는 것이다. PostgreSQL·SQLite는 INSERT ... ON CONFLICT, MySQL은 INSERT ... ON DUPLICATE KEY UPDATE, 표준 SQL은 MERGE로 지원한다. 조회해서 있으면 수정하고 없으면 추가하는 코드를 애플리케이션에서 짜지 않아도 되게 해주는, 은근히 자주 쓰는 기능이다.
왜 굳이 한 문장이어야 하냐면, "먼저 SELECT로 확인하고 없으면 INSERT" 패턴은 경쟁 조건(race condition)에 취약하기 때문이다. 두 요청이 동시에 "없네" 하고 둘 다 INSERT하면 중복이나 유니크 위반이 터진다. UPSERT는 이걸 DB가 원자적으로 막아준다.
ON CONFLICT의 기본
PostgreSQL을 기준으로 보자. 충돌 판단의 기준이 되는 유니크 제약 또는 유니크 인덱스가 반드시 있어야 동작한다. 이게 없으면 무엇을 "중복"으로 볼지 DB가 모른다.
-- email에 유니크 제약이 있다고 가정
INSERT INTO users (email, name, login_count)
VALUES ('a@ex.com', '민수', 1)
ON CONFLICT (email)
DO UPDATE SET
name = EXCLUDED.name,
login_count = users.login_count + 1;여기서 EXCLUDED가 포인트다. 이건 INSERT하려다 충돌해서 밀려난 그 값을 가리키는 가상 테이블이다. 위에서 EXCLUDED.name은 새로 넣으려던 '민수'이고, users.login_count는 기존 행의 값이다. 그래서 로그인 횟수를 1 증가시키는 로직이 자연스럽게 표현된다. 나는 이 EXCLUDED 개념을 처음에 헷갈려서 값이 이상하게 들어간 적이 있다.
충돌 시 그냥 무시하고 싶을 때
수정까지 필요 없고 "이미 있으면 넘어가"만 원할 때가 많다. 로그 적재, 태그 등록 같은 곳이다. 이럴 땐 DO NOTHING이다.
-- 이미 있으면 조용히 건너뛴다 (에러 안 남)
INSERT INTO tags (name)
VALUES ('database'), ('index'), ('sql')
ON CONFLICT (name) DO NOTHING;여러 행을 한 번에 넣으면서 이미 있는 것만 건너뛰기 좋다. 유니크 위반 예외를 try/catch로 잡는 것보다 훨씬 깔끔하고 빠르다.
방언 비교
| DB | 문법 | 새 값 참조 |
|---|---|---|
| PostgreSQL / SQLite | INSERT ... ON CONFLICT DO UPDATE | EXCLUDED.col |
| MySQL / MariaDB | INSERT ... ON DUPLICATE KEY UPDATE | VALUES(col) 또는 별칭 |
| 표준 / SQL Server / Oracle | MERGE INTO ... WHEN MATCHED | source 별칭 |
MySQL은 특정 유니크 키를 지정하지 않고 모든 유니크 키에 대해 충돌을 본다는 점이 PostgreSQL과 다르다. 유니크 제약이 여러 개인 테이블에서는 어느 키에 부딪쳤는지 헷갈릴 수 있으니 조심해야 한다.
흔한 함정
- 충돌 대상 컬럼에 유니크 제약이 없으면 ON CONFLICT는 에러를 낸다. 인덱스나 제약을 먼저 확인하라.
- PostgreSQL의 UPSERT는 충돌이 잦으면 실패한 INSERT 시도가 시퀀스 값을 소모한다. 자동증가 ID에 구멍이 생겨도 놀라지 마라. 정상이다.
DO UPDATE에WHERE를 붙여 조건부 갱신도 된다. 예:DO UPDATE SET price = EXCLUDED.price WHERE users.updated_at < EXCLUDED.updated_at— 더 최신일 때만 덮어쓰기.- 대량 동기화에서 UPSERT를 배치로 묶으면 왕복이 줄어 훨씬 빠르다. 다만 배치가 너무 크면 락 경합이 늘 수 있으니 적당히 쪼갠다.
개인적으로 외부 데이터를 주기적으로 당겨와 우리 테이블에 반영하는 동기화 작업에서 UPSERT를 가장 애용한다. "있으면 갱신, 없으면 추가"를 안전하고 짧게 표현할 수 있어서, 이걸 알기 전에 쓰던 select-후-분기 코드가 얼마나 장황했는지 지금 보면 아득하다.
자주 묻는 질문
ON CONFLICT는 어떤 컬럼을 기준으로 충돌을 판단하나요?
지정한 컬럼(또는 제약)의 유니크 인덱스를 기준으로 합니다. ON CONFLICT (email)이면 email 유니크 인덱스에 부딪칠 때만 UPDATE로 전환됩니다. 대상 컬럼에 유니크 제약이 없으면 사용할 수 없습니다.
EXCLUDED와 기존 행 값을 어떻게 구분하나요?
EXCLUDED.col은 INSERT하려던 새 값, 테이블명.col은 이미 존재하던 기존 값입니다. 누적 카운트처럼 기존 값에 새 값을 더할 때 이 둘을 조합합니다.
UPSERT로 넣었는지 갱신했는지 구분할 수 있나요?
PostgreSQL에서는 RETURNING과 시스템 컬럼 xmax를 활용하거나, 삽입·수정 여부를 나타내는 표현식을 함께 반환해 구분하는 기법이 있습니다. 다만 방식이 다소 우회적이라, 정말 필요할 때만 씁니다.
MERGE와 ON CONFLICT 중 무엇을 써야 하나요?
단순한 삽입/갱신이면 ON CONFLICT가 간결합니다. 삽입·수정·삭제를 조건별로 복합 처리하는 데이터 웨어하우스류 작업이면 MERGE가 표현력이 좋습니다. 이식성을 중시한다면 표준인 MERGE가 유리하지만, DB별 지원 시점과 동작 차이는 확인해야 합니다.
댓글 0