PostgreSQL에는 UPSERT 키워드가 없습니다. 대신 INSERT ... ON CONFLICT (column) DO UPDATE SET column = EXCLUDED.column으로 씁니다. 새 행이 그 컬럼의 고유 제약 조건을 위반하게 되면 PostgreSQL은 기존 행을 대신 업데이트합니다. DO NOTHING은 그 행을 건너뜁니다.
A-100은 이미 있었으므로 가격이 12.5로 바뀌었습니다. B-200은 새 행이라 추가되었습니다. 문장 하나가 두 경우를 모두 처리하며, 다른 세션이 같은 키를 동시에 넣어도 올바르게 동작합니다.
행 하나를 INSERT 또는 UPDATE하기
이 문장은 세 부분으로 되어 있습니다.
INSERT INTO table_name (columns) VALUES (...)
ON CONFLICT (conflict_target)
DO UPDATE SET column = value [, ...]
[WHERE condition];
- 충돌 대상은 기본 키처럼 고유 인덱스나 제약 조건의 컬럼을 지정합니다. 정확히 일치해야 하며, 그렇지 않으면 PostgreSQL이 "there is no unique or exclusion constraint matching the ON CONFLICT specification" 오류를 냅니다. 제약 조건 이름을 대신 쓸 수도 있습니다.
ON CONFLICT ON CONSTRAINT products_pkey처럼 씁니다. - **
EXCLUDED**는 넣으려던 행입니다.EXCLUDED.price는 새 가격이고,products.price는 이미 저장된 가격입니다. - **
DO UPDATE SET**은 나열한 컬럼만 바꿉니다. 기존 행의 다른 컬럼은 값을 유지합니다.
WHERE 절이 있는 고유 인덱스(부분 인덱스)도 그 조건을 반복해 쓰면 충돌 대상이 될 수 있습니다. ON CONFLICT (email) WHERE deleted_at IS NULL처럼 씁니다.
DO NOTHING으로 중복 건너뛰기
DO NOTHING은 새 행은 추가하고 충돌하는 행은 오류 없이 버립니다. 충돌 대상이 없으면 어떤 고유 제약 조건이든 위반하는 행을 건너뜁니다.
RETURNING에는 lin@example.com만 나옵니다. 건너뛴 행은 반환되지 않으므로, DO NOTHING ... RETURNING으로 어떤 행이 새로 들어갔는지 알 수 있습니다.
카운터 더하기
저장된 값을 바꾸지 않고 늘리려면 현재 값에 EXCLUDED를 더합니다. 현재 값에는 테이블 이름을 붙이세요. DO UPDATE 안에서 views만 쓰면 테이블과 EXCLUDED 양쪽에 맞으므로, PostgreSQL이 "column reference "views" is ambiguous" 오류로 멈춥니다.
첫 문장은 1로 행을 추가하고 다음 두 문장이 더하므로, /home은 3이 됩니다.
값이 바뀌었을 때만 업데이트하기
DO UPDATE SET 뒤의 WHERE는 행마다 업데이트를 실행할지 정합니다. 거짓이면 행은 그대로 남고, 추가된 것도 업데이트된 것도 아닌 것으로 칩니다. 최신 데이터만 남기거나, 바뀐 것이 없을 때 새 행 버전을 쓰지 않으려고 씁니다.
들어온 행이 더 오래되었으므로 A-100은 10을 유지합니다. B-200은 25가 됩니다. 같은 값을 다시 쓰는 업데이트를 건너뛰려면 WHERE prices.price IS DISTINCT FROM EXCLUDED.price를 쓰세요. NULL도 처리합니다.
추가된 행과 업데이트된 행 구분하기
RETURNING은 다른 INSERT처럼 upsert에서도 동작합니다. 두 경우를 구분하려면 시스템 컬럼 xmax를 읽으세요. 이 문장이 추가한 행이면 0입니다.
A-100은 8과 inserted false로, C-300은 7과 true로 반환됩니다. xmax는 문서화된 API가 아니라 내부 구현이므로 편의 기능으로만 쓰세요. 이 절 전체는 RETURNING 페이지에서 다룹니다.
자주 만나는 오류
- "ON CONFLICT DO UPDATE command cannot affect row a second time": 한 문장의
VALUES나SELECT에 같은 키가 두 번 나옵니다. 예를 들어SELECT DISTINCT ON (sku) ...로 먼저 중복을 없애세요. - "column reference is ambiguous": 위에서처럼
SET식과WHERE에서 컬럼에 테이블 이름을 붙이세요. - identity나 serial id의 빈 번호: UPDATE로 바뀐 INSERT도 이미 시퀀스에서 값을 가져갔으므로 id가 건너뜁니다. 정상입니다.
MERGE(PostgreSQL 15 이상)도 INSERT나 UPDATE를 할 수 있지만, 두 세션이 같은 키를 동시에 넣는 경우를 막아 주지 않습니다. 키 기준의 upsert에는 ON CONFLICT를 쓰세요. 모든 옵션은 공식 INSERT 레퍼런스에 있습니다.
자주 묻는 질문
PostgreSQL에 UPSERT 문이 있나요?
UPSERT 키워드는 없습니다. PostgreSQL 9.5에 INSERT ... ON CONFLICT가 추가되었습니다. 행을 추가하되, 고유 제약 조건을 위반하게 되면 기존 행을 업데이트하거나(DO UPDATE) 건너뜁니다(DO NOTHING).
INSERT ... ON CONFLICT는 동시 INSERT에서도 안전한가요?
네. 다른 세션이 같은 순간에 같은 키를 넣어도, 넣으려는 각 행은 INSERT나 UPDATE 중 하나로 끝납니다. 애플리케이션 코드에서 SELECT 뒤에 INSERT나 UPDATE를 하는 방식은 이를 보장하지 않습니다.
upsert에는 MERGE와 ON CONFLICT 중 무엇을 써야 하나요?
키 기준의 INSERT 또는 UPDATE에는 ON CONFLICT를 쓰세요. MERGE(PostgreSQL 15 이상)는 일치하는 행 삭제처럼 더 많은 경우를 처리합니다. 다만 같은 키를 동시에 INSERT하면 고유 제약 위반으로 실패할 수 있으며, ON CONFLICT는 이를 피합니다.
"there is no unique or exclusion constraint matching the ON CONFLICT specification" 오류는 왜 나나요?
ON CONFLICT (...)의 컬럼은 고유 인덱스나 제약 조건과 정확히 일치해야 합니다. 그 컬럼에 UNIQUE 제약 조건이나 고유 인덱스를 추가하거나, 이미 있는 것의 컬럼을 지정하세요.