업서트(INSERT ... ON CONFLICT)
PostgreSQL에서 INSERT ... ON CONFLICT로 업서트하려면 어떻게 하나요?
INSERT ... ON CONFLICT는 행을 삽입하거나, 삽입이 고유 제약 조건을 위반하게 되면 기존 행을 업데이트하거나 건너뛰는 작업을 하나의 원자적 단계로 수행합니다.
업서트(upsert)는 삽입 또는 업데이트를 뜻합니다. PostgreSQL은 이를 INSERT ... ON CONFLICT로 구현합니다. 새 행이 고유 제약 조건이나 배제 제약 조건을 위반하게 되면 DO NOTHING은 해당 행을 건너뛰고, DO UPDATE는 대신 충돌한 행을 업데이트합니다.
ON CONFLICT DO UPDATE는 동시 세션이 있어도 삽입 또는 업데이트라는 원자적 결과를 보장하므로, SELECT를 실행한 뒤 INSERT나 UPDATE를 따로 실행할 필요가 없습니다. 특수 테이블 EXCLUDED는 삽입하려고 제안된 행을 가리킵니다. (path)와 같은 충돌 대상은 고유 인덱스나 제약 조건과 일치해야 합니다.
예제
이 예제는 같은 업서트를 두 번 실행합니다. 첫 번째는 조회수 1로 /home을 삽입하고, 두 번째는 기본 키와 충돌해 1을 더하므로 최종 조회수는 2입니다.
CREATE TABLE page_views (path text PRIMARY KEY, views bigint NOT NULL);
INSERT INTO page_views VALUES ('/home', 1)
ON CONFLICT (path) DO UPDATE SET views = page_views.views + EXCLUDED.views;
INSERT INTO page_views VALUES ('/home', 1)
ON CONFLICT (path) DO UPDATE SET views = page_views.views + EXCLUDED.views;
SELECT * FROM page_views;확장 없이 기본 PostgreSQL 16 또는 17에서 실행되는 일반 SQL입니다. 버려도 되는 빈 데이터베이스에서 실행하세요.
출처
PostgreSQL 공식 문서(영문)입니다.