있으면 수정, 없으면 추가하기 — sqlite upsert
실무에서 정말 자주 마주치는 상황이 있습니다. 새 행을 넣고 싶은데, 같은 키를 가진 행이 이미 있다면 그땐 갱신하고 싶다는 거죠. UPSERT가 없다면 먼저 SELECT로 존재 여부를 확인하고, 결과에 따라 INSERT나 UPDATE로 갈라져야 합니다. 쿼리도 두 번 날아가고, 그 사이에 다른 트랜잭션이 끼어드는 경쟁 조건(race condition)까지 신경 써야 하니 골치가 아픕니다.
sqlite의 upsert를 쓰면 이 모든 걸 단 한 문장으로 해결할 수 있습니다:
처음 실행하면 새 행이 들어갑니다. 같은 sku로 가격만 바꿔서 다시 실행하면, 기존 행이 그 자리에서 그대로 갱신되죠. 중복도 안 생기고, 에러도 안 납니다.
ON CONFLICT 구문 뜯어보기
전체 구조는 이렇게 생겼습니다:
INSERT INTO table (...) VALUES (...)
ON CONFLICT(conflict_target) DO UPDATE SET col = expr, ...
WHERE condition;
핵심은 세 가지입니다.
conflict_target— 충돌이 예상되는UNIQUE또는PRIMARY KEY제약이 걸린 컬럼(들)입니다. SQLite는 이 정보를 보고 어떤 인덱스를 감시할지 결정합니다.DO UPDATE SET ...— 충돌이 발생했을 때 기존 행을 어떻게 갱신할지 지정합니다. (조용히 넘어가고 싶다면DO NOTHING을 쓰면 됩니다.)- 선택적인
WHERE— 이 조건이 참일 때만 실제로 UPDATE가 실행됩니다.
conflict target은 실제로 존재하는 unique 제약과 일치해야 합니다. price 컬럼에 unique 제약이 없다면 ON CONFLICT(price)는 아예 컴파일되지 않습니다. 충돌을 감지할 기준 자체가 없기 때문이죠.
DO NOTHING: 없으면 INSERT, 있으면 그냥 넘어가기
더 단순한 형태입니다. 초기 데이터를 채워 넣거나 이벤트 로그를 기록할 때처럼 중복은 그냥 조용히 무시하고 싶을 때 유용합니다.
두 번째 INSERT는 같은 event_id를 사용하기 때문에 원래대로라면 UNIQUE constraint failed 오류가 발생해야 합니다. 하지만 DO NOTHING을 붙이면 SQLite는 이 INSERT를 그냥 건너뜁니다. 예외도 없고, 변경된 행도 없습니다.
흔히 멱등성(idempotent) 있는 INSERT를 구현할 때 INSERT OR IGNORE를 떠올리곤 하는데, UPSERT의 DO NOTHING도 동일한 역할을 합니다. 게다가 WHERE나 RETURNING 절과 함께 쓰기에 훨씬 유연합니다.
excluded 의사 테이블 활용하기
충돌이 발생하는 순간, 두 개의 행이 동시에 등장합니다. 테이블에 이미 있던 기존 행과, 새로 INSERT하려다 거절된 행이죠. SQLite는 이 두 행을 각각 가리킬 수 있는 방법을 제공합니다.
- 컬럼명을 그대로 적으면(
price,name) 기존 행을 가리킵니다. excluded.column형태로 적으면 거절된 새로 들어오는 행을 가리킵니다.
quantity = quantity + excluded.quantity는 "기존 수량 + 새로 들어온 수량"으로 읽으면 됩니다. 두 번 INSERT한 뒤 A-100의 quantity는 8이 되죠. 이렇게 기존 행에 값을 누적시키는 패턴은 UPSERT가 보여주는 가장 유용한 활용법 중 하나입니다.
WHERE 절로 조건부 UPSERT 처리하기
UPSERT 끝에 WHERE를 붙이면 특정 조건을 만족할 때만 UPDATE가 실행되고, 그렇지 않으면 건너뜁니다. 이 조건은 기존 행을 기준으로 평가되며, 새로 들어오는 값은 excluded.*로 참조할 수 있습니다:
새로 들어오는 행의 updated_at이 더 오래된 값이라 WHERE 조건이 false가 되고, 결국 업데이트는 건너뜁니다. 기존 행은 더 최신 가격을 그대로 유지하죠. 날짜를 반대로 바꿔 넣으면 그제서야 업데이트가 실행됩니다. "더 신선한 데이터일 때만 덮어쓴다"는 전형적인 패턴입니다.
여러 행을 한 번에 upsert 하기
VALUES 절에는 여러 행을 한꺼번에 넣을 수 있고, ON CONFLICT는 각 행마다 독립적으로 적용됩니다:
A-100은 충돌이 발생해서 업데이트되고, A-200과 A-300은 새 데이터라 삽입됩니다. 한 문장으로 삽입과 업데이트가 동시에 처리되는 거죠. 외부 소스에서 가져온 레코드를 일괄로 동기화할 때 아주 깔끔한 방법입니다.
UPSERT와 INSERT OR REPLACE의 차이
겉보기에는 INSERT OR REPLACE도 똑같은 일을 하는 것처럼 보이지만, 실제로는 전혀 다릅니다.
notes가 사라져 버렸습니다. INSERT OR REPLACE는 1번 행을 통째로 삭제한 뒤 새로 insert하기 때문에, 명시하지 않은 컬럼은 모두 NULL이나 기본값으로 초기화됩니다. 게다가 DELETE 트리거가 발동되고 ON DELETE 외래 키를 통한 cascade까지 줄줄이 일어납니다.
반면 UPSERT는 기존 행을 그대로 보존합니다:
notes 컬럼은 그대로 남아 있죠. SET에 명시한 컬럼만 바뀝니다. 기본적으로는 UPSERT를 쓰고, 정말로 삭제 후 재삽입 동작이 필요할 때만 INSERT OR REPLACE를 쓰세요.
여러 개의 충돌 대상 지정하기
한 행이 둘 이상의 제약 조건에서 충돌할 가능성이 있다면, ON CONFLICT 절을 여러 번 이어 붙일 수 있습니다:
먼저 걸리는 제약이 우선순위를 가지며, 해당 분기의 DO UPDATE가 실행됩니다. 실무에서는 대부분의 테이블이 명확한 충돌 대상 하나만 갖습니다 — 기본 키이거나 단일 UNIQUE 컬럼이죠. 절을 두 개 이상 쓸 일은 거의 없습니다.
자주 빠지는 함정
UPSERT를 쓰면서 흔히 마주치는 문제들입니다:
- 매칭되는 UNIQUE 인덱스가 없으면 UPSERT도 없습니다.
ON CONFLICT(col)을 쓰려면col이PRIMARY KEY이거나UNIQUE제약을 가져야 합니다. 그렇지 않으면 SQLite가 "no such constraint" 에러를 뱉습니다. - 충돌이 없으면
DO UPDATE는 발동하지 않습니다. INSERT에 더해지는 동작이 아니라 INSERT의 대체 동작입니다. 키가 처음 들어올 땐 INSERT만 실행됩니다. excluded는 읽기 전용입니다. 값을 읽어올 수는 있지만 쓸 수는 없습니다.SET의 대상은 항상 기존 행입니다.- 자동 생성되는
INTEGER PRIMARY KEYrowid에 주의하세요. id를 직접 지정하지 않으면 INSERT마다 새 값이 부여되므로 충돌할 대상 자체가 없습니다. UPSERT는 충돌 대상 컬럼 값을 호출자가 결정적으로 넘겨줄 때만 의미가 있습니다.
다음: RETURNING
UPSERT만으로는 어떤 행이 새로 INSERT됐고 어떤 행이 UPDATE됐는지, 또 최종 값이 뭔지 알 수 없습니다. 이럴 때 쓰는 것이 RETURNING 절입니다. 같은 문장 안에서 영향받은 행을 그대로 돌려주기 때문에 별도의 SELECT가 필요 없죠. 다음 글에서 다뤄보겠습니다.
자주 묻는 질문
SQLite의 UPSERT가 정확히 뭔가요?
한마디로 INSERT를 시도하다가 UNIQUE나 PRIMARY KEY 제약을 위반하면 UPDATE로 바뀌거나(또는 아무 일도 안 하도록) 동작하는 구문입니다. 문법은 INSERT ... ON CONFLICT(컬럼) DO UPDATE SET ... 또는 DO NOTHING 형태로 씁니다. SQLite에는 3.24.0 버전(2018년)부터 들어왔습니다.
UPSERT에 나오는 excluded 테이블은 뭔가요?
excluded는 "방금 INSERT 하려다 거절당한 그 행"을 담아두는 가상의 의사 테이블입니다. DO UPDATE SET ... 안에서는 컬럼 이름만 쓰면 기존 행을 가리키고, excluded.컬럼 형태로 쓰면 새로 들어오려던 값을 가리킵니다. 예를 들어 SET price = excluded.price는 "price를 새 INSERT가 가져온 값으로 덮어써라"는 뜻이 됩니다.
INSERT OR REPLACE랑 UPSERT는 어떻게 다른가요?
INSERT OR REPLACE는 충돌이 나는 행을 아예 삭제하고 새 행을 끼워 넣는 방식입니다. 그래서 DELETE 트리거가 발동되고, ON DELETE CASCADE가 걸린 외래 키가 줄줄이 같이 날아가며, 명시하지 않은 컬럼은 모두 기본값으로 초기화됩니다. 반면 UPSERT는 기존 행을 그 자리에서 업데이트하기 때문에 SET에 적은 컬럼만 바뀝니다. 정말로 "지우고 다시 넣기"가 필요한 게 아니라면 UPSERT를 쓰는 게 안전합니다.
한 번에 여러 행을 UPSERT 할 수 있나요?
네, 됩니다. INSERT INTO t(...) VALUES (...), (...), (...) ON CONFLICT(col) DO UPDATE SET ... 형태로 그대로 쓰면 됩니다. 각 행마다 충돌 대상이 개별적으로 검사되고, DO UPDATE 안의 excluded 행은 그때 충돌을 일으킨 바로 그 행을 가리킵니다.
