[PostgreSQL] 데이터가 있으면 업데이트, 없으면 인서트? ON CONFLICT 완벽 정리 (feat. DELETE 전략)

데이터베이스를 다루다 보면 가장 흔하게 마주치는 로직이 있습니다. "데이터가 이미 있으면 정보를 수정하고, 없으면 새로 넣어줘(Upsert)."

과거에는 이를 위해 Java 코드에서 SELECT를 먼저 날려보고 IF문으로 분기 처리를 하곤 했죠. 하지만 PostgreSQL의 ON CONFLICT 구문을 사용하면 쿼리 한 줄로, 그것도 0.004초의 찰나에 발생하는 동시성 이슈까지 완벽하게 방지하며 처리할 수 있습니다.

---

1. ON CONFLICT란? (Upsert의 정석)

ON CONFLICT는 INSERT를 시도하다가 유니크 인덱스나 프라이머리 키(PK) 충돌이 발생했을 때, 에러를 내는 대신 대안적인 동작(DO UPDATE 또는 DO NOTHING)을 수행하도록 지시하는 구문입니다.

• 장점: 네트워크 왕복 횟수 감소, 원자성(Atomicity) 보장, 동시성 이슈 해결.

---

2. 기본 사용법: DO UPDATE (데이터 수정)

가장 기본적인 형태입니다. EXCLUDED라는 가상 테이블을 통해 새로 입력하려 했던 값을 참조할 수 있습니다.

SQL

 

INSERT INTO human (emp_no, name, position, updated_at)
VALUES ('2026001', '홍길동', '대리', NOW())
ON CONFLICT (emp_no) -- 충돌을 감시할 컬럼 (유니크 인덱스 필수)
DO UPDATE SET
    name = EXCLUDED.name,
    position = EXCLUDED.position,
    updated_at = EXCLUDED.updated_at;

---

3. 응용편: 특정 조건에서만 업데이트하기 (WHERE)

모든 충돌에서 업데이트를 하는 게 아니라, "기존 데이터와 값이 다를 때만" 업데이트하고 싶다면 WHERE 절을 붙일 수 있습니다. 이때 아까 기억해둔 IS DISTINCT FROM이 빛을 발합니다.

SQL

 

ON CONFLICT (emp_no)
DO UPDATE SET
    position = EXCLUDED.position,
    updated_at = NOW()
WHERE human.position IS DISTINCT FROM EXCLUDED.position;
-- 기존 직급과 새로 들어온 직급이 '다를 때만' 업데이트 실행 (불필요한 쓰기 방지)

---

4. 심화편: ON CONFLICT로 'DELETE' 효과 내기

PostgreSQL의 ON CONFLICT 구문 자체에는 DO DELETE라는 명령어가 없습니다. 하지만 실무에서는 "충돌 발생 시 특정 플래그를 변경하여 삭제된 것처럼 처리(Soft Delete)"하거나 "만료 처리를 하는 방식"으로 DELETE 효과를 냅니다.

예시: 상태값을 바꿔서 '삭제' 처리하기

예를 들어, 배치가 돌면서 "이미 존재하는 데이터가 들어오면 사용 중지(use_yn = 'N') 처리를 해라"는 요건이 있다면 아래와 같이 작성합니다.

SQL

 

INSERT INTO tb_batch_target (target_id, status)
VALUES ('TGT_001', 'ACTIVE')
ON CONFLICT (target_id)
DO UPDATE SET
    status = 'DELETED',   -- 충돌 시 삭제 상태로 변경
    deleted_at = NOW()    -- 삭제 일시 기록
WHERE tb_batch_target.status != 'DELETED';

만약 진짜로 데이터를 삭제(Hard Delete)해야 한다면?

ON CONFLICT는 기본적으로 INSERT 문이기 때문에 직접 삭제는 불가능합니다. 이럴 때는 보통 **WITH문(CTE)**을 활용하거나, 임시 테이블에 먼저 넣은 뒤 DELETE와 INSERT를 순차적으로 실행하는 전략을 씁니다.

---

5. 실무자들을 위한 체크리스트

1. 제약조건(Constraint) 필수: ON CONFLICT를 쓰려면 충돌을 판단할 기준이 되는 유니크 인덱스나 PK가 반드시 있어야 합니다.
2. EXCLUDED 테이블: 이 테이블은 INSERT 하려고 했던 '새로운 데이터'를 담고 있습니다. 기존 데이터를 참조할 때는 원래 테이블 이름을 사용하세요.
3. 성능 최적화: WHERE 절을 적극 활용하여 데이터 변경이 없는 경우에는 UPDATE가 발생하지 않도록 제어하는 것이 DB 부하를 줄이는 핵심입니다.

---

결론

ON CONFLICT는 단순한 문법을 넘어, 고가용성 배치를 설계할 때 필수적인 도구입니다. 특히 데이터의 존재 여부를 확인하고 처리하는 과정을 DB 엔진 단에서 처리함으로써, 애플리케이션 레벨의 복잡성을 획기적으로 낮춰줍니다.

오늘 소개한 DO UPDATE 전략과 WHERE 조건을 통한 필터링을 적절히 조합하여, 더 견고하고 빠른 데이터 파이프라인을 구축해 보세요!