숏컷 코드
INSERT INTO users (email, name)
VALUES ('ada@example.com', 'Ada')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name
RETURNING id, email, name;문법
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 를 쓰려면 충돌 기준이 되는 UNIQUE 제약 또는 PRIMARY KEY 가 먼저 스키마에 선언되어 있어야 합니다. 충돌이 발생하면 DO NOTHING(무시)이나 DO UPDATE(갱신) 중 하나를 선택합니다. DO UPDATE 절 안에서 EXCLUDED 는 삽입하려 했으나 충돌로 거부된 행의 값을 참조하는 특별한 가상 테이블입니다. 이 메커니즘은 PostgreSQL 9.5 이상에서 사용 가능하며, INSERT ... ON CONFLICT 전체가 원자적으로 실행됩니다.
-- 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 두 단계로 나눠 처리하면 두 세션이 동시에 같은 행을 삽입하려 할 때 경쟁 조건이 생깁니다. ON CONFLICT 는 이 문제를 제거하는 대신, 충돌 검사 시점에 해당 행 또는 인덱스 항목에 잠금을 획득합니다. 고빈도 upsert 가 같은 키에 집중되면 잠금 경합이 병목이 될 수 있습니다.
-- 집계 테이블 누적 업데이트 패턴
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;선택 기준
| 상황 | 적합한 선택 |
|---|---|
| 삽입 후 생성된 id 를 즉시 알아야 할 때 | INSERT ... RETURNING id |
| 중복 삽입 시 조용히 건너뛰고 싶을 때 | ON CONFLICT DO NOTHING |
| 중복이면 특정 컬럼만 갱신하고 싶을 때 | ON CONFLICT DO UPDATE SET ... |
| 삽입되거나 갱신된 행의 값을 반환받을 때 | ON CONFLICT DO UPDATE ... RETURNING |
| 다단계 쓰기를 단일 쿼리로 묶을 때 | WITH ... RETURNING CTE 파이프라인 |
주의할 점
ON CONFLICT 를 사용하려면 충돌 기준 컬럼에 UNIQUE 제약 또는 PRIMARY KEY 가 반드시 스키마에 선언되어 있어야 합니다.
제약이 없는 컬럼을 ON CONFLICT 기준으로 쓰면 오류가 발생합니다.
또한 DO NOTHING 은 충돌 시 영향 행 수가 0 으로 반환되므로, 삽입 여부를 확인하려면 RETURNING 결과가 비어 있는지로 판단해야 합니다.
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와 기존 테이블 값을 함께 써서 의도를 명시해야 합니다.
참고 링크
1 sources