Quick Reference

INSERT INTO users (email, name)
VALUES ('ada@example.com', 'Ada')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name
RETURNING id, email, name;

새 행의 기본값이나 갱신 결과가 바로 필요하면 RETURNING을 붙입니다. 고유성 충돌을 무시하면 DO NOTHING, 기존 행을 바꿔야 하면 DO UPDATE를 선택합니다. DO UPDATE의 충돌 대상은 추론 가능한 고유 인덱스 또는 NOT DEFERRABLE 고유 제약이어야 합니다.

문법

RETURNING 은 추가 SELECT 없이 변경된 행을 즉시 반환한다

INSERT, UPDATE, DELETE 뒤에 RETURNING 절을 붙이면 해당 명령이 실제로 영향을 준 행의 데이터를 바로 돌려받을 수 있습니다. 애플리케이션에서 새로 삽입된 id 를 얻거나 수정된 값을 확인할 때 별도의 SELECT 를 추가 실행할 필요가 없어집니다. 이는 왕복 횟수(round trip)를 줄이고, 시퀀스 기반 id 가 어떤 값으로 생성됐는지 경쟁 조건 없이 알 수 있게 해 주는 실질적인 이점입니다.

-- 삽입 후 생성된 id 즉시 획득
INSERT INTO posts (title, user_id)
VALUES ('새 글', 42)
RETURNING id, created_at;

-- DELETE 후 삭제된 행 목록 반환
DELETE FROM sessions
WHERE expires_at < NOW()
RETURNING user_id, expires_at;

RETURNING과 UPSERT는 "바로 돌려받기"와 "충돌 시 처리"로 나눠서 보면 된다

RETURNING은 DML 결과를 곧바로 읽는 기능이고, UPSERT는 충돌 시 무시하거나 갱신하는 기능입니다. 둘은 자주 같이 쓰이지만 서로 다른 문제를 해결합니다. 먼저 "충돌 처리"가 필요한지, "변경 직후 값"이 필요한지 분리해서 보면 읽기 쉽습니다.

-- 값만 바로 돌려받기
INSERT INTO users (email, name)
VALUES ('ada@example.com', 'Ada')
RETURNING id;

-- 충돌 시 갱신
INSERT INTO users (email, name)
VALUES ('ada@example.com', 'Ada')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;

ON CONFLICT는 고유 인덱스 또는 제약을 충돌 기준으로 사용한다

ON CONFLICT는 충돌을 판단할 고유 인덱스 또는 고유 제약을 전제로 합니다. 충돌이 발생하면 DO NOTHING(무시)이나 DO UPDATE(갱신)를 선택합니다. DO UPDATE에서는 충돌 대상을 지정해야 하고, NOT DEFERRABLE 고유 제약 또는 고유 인덱스만 arbiter로 사용할 수 있습니다. EXCLUDED는 삽입하려 했으나 충돌한 행의 값을 참조하는 특별한 이름입니다. INSERT ... ON CONFLICT DO UPDATE는 독립된 오류가 없는 한 높은 동시성에서도 삽입 또는 갱신 중 하나가 원자적으로 일어납니다.

-- DO NOTHING: 충돌 시 조용히 무시 (영향 행 수 = 0)
INSERT INTO event_log (event_id, user_id)
VALUES (100, 42)
ON CONFLICT (event_id, user_id) DO NOTHING;

-- DO UPDATE: EXCLUDED 로 새 값 참조
INSERT INTO user_stats (user_id, login_count, last_login)
VALUES (42, 1, NOW())
ON CONFLICT (user_id)
DO UPDATE SET
  login_count = user_stats.login_count + 1,
  last_login = EXCLUDED.last_login;

DO NOTHING 과 DO UPDATE 는 충돌을 무시할지 반영할지의 차이다

중복 이벤트처럼 "이미 있으면 건너뛰면 끝"이면 DO NOTHING, 누적 통계나 최신 상태처럼 "충돌 시 값을 갱신해야" 하면 DO UPDATE가 맞습니다. 둘 다 UPSERT처럼 보이지만, 결과 행과 후속 로직은 완전히 다르므로 먼저 충돌 뒤 원하는 상태를 정하는 편이 안전합니다.

-- 중복이면 무시
INSERT INTO processed_events (event_id)
VALUES ('evt_123')
ON CONFLICT (event_id) DO NOTHING;

-- 중복이면 최신 값 반영
INSERT INTO user_profiles (user_id, nickname)
VALUES (42, 'ada')
ON CONFLICT (user_id)
DO UPDATE SET nickname = EXCLUDED.nickname;

UPSERT 의 원자성은 경쟁 조건을 방지하지만 잠금 비용이 있다

INSERT ... ON CONFLICT DO UPDATE는 단일 SQL 문 안에서 원자적으로 처리됩니다. 애플리케이션에서 SELECT 뒤에 INSERT 또는 UPDATE를 고르면 두 세션이 같은 행을 판단하는 경쟁 조건이 생깁니다. 다만 같은 충돌 키로 요청이 몰리면 행 잠금 대기가 생길 수 있습니다. DO UPDATE ... WHERE 조건이 거짓이어도 충돌 행은 잠길 수 있고 RETURNING에도 나오지 않으므로, hot key는 배치·집계 설계와 실제 잠금 대기를 함께 점검합니다.

-- 집계 테이블 누적 업데이트 패턴
INSERT INTO daily_stats (date, user_id, page_views)
VALUES (CURRENT_DATE, 42, 1)
ON CONFLICT (date, user_id)
DO UPDATE SET page_views = daily_stats.page_views + EXCLUDED.page_views
RETURNING date, user_id, page_views;

RETURNING 은 CTE 와 결합해 다단계 쓰기 파이프라인을 구성할 수 있다

RETURNING 결과를 WITH 절에서 받아 다음 쓰기 명령의 입력으로 연결하면 여러 테이블에 걸친 쓰기 작업을 단일 쿼리로 표현할 수 있습니다. 이 패턴은 트랜잭션 안에서 같은 효과를 낼 수 있지만, 단일 쿼리로 구성하면 왕복 횟수를 더 줄일 수 있습니다.

-- INSERT 결과를 받아 즉시 연관 테이블에도 삽입
WITH new_user AS (
  INSERT INTO users (email, name)
  VALUES ('bob@example.com', 'Bob')
  RETURNING id
)
INSERT INTO user_profiles (user_id, bio)
SELECT id, ''
FROM new_user;

선택 기준

주의할 점

INSERT INTO users (name)
VALUES ('Ada')
ON CONFLICT (name) DO NOTHING;

name에 UNIQUE 제약이 없으면 이 구문은 동작하지 않습니다. ON CONFLICT는 "쿼리에서 임의로 충돌 기준을 고르는 기능"이 아니라 "이미 선언된 고유성 제약을 활용하는 기능"입니다.

INSERT INTO user_stats (user_id, login_count)
VALUES (42, 1)
ON CONFLICT (user_id)
DO UPDATE SET login_count = 1;

누적 카운터를 올리는 문제에서 이렇게 기존 값을 덮어쓰면, 충돌은 막아도 의미는 틀립니다. 합산·최댓값·최신 시각처럼 갱신 규칙이 있는 컬럼은 EXCLUDED와 기존 테이블 값을 함께 써서 의도를 명시해야 합니다.