[PostgreSQL] INSERT ~ ON CONFLICT (UPSERT)

Database

[PostgreSQL] INSERT ~ ON CONFLICT (UPSERT)

비번변경 2025. 9. 3. 09:58

개요

이 블로그에서 UPSERT에 대해서 적게나마 다뤘는데, PostgreSQL도 MySQL처럼 UPSERT를 지원한다. 다만 문법에 조금 차이가 있는데, 이번 글에서는 PostgreSQL에서 UPSERT를 수행하는 방법을 적어둔다.

INSERT ~ ON CONFLICT

ON CONFLICT 절은 UNIQUE 또는 EXCLUSION 제약 위배를 해소하기 위한 대체 동작을 지정한다.

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_target)
conflict_action

INSERT 하는 행에 대해 conflict_target에 지정한 제약 조건이나 인덱스가 위배되었을 때 conflict_action을 수행한다.

conflict_target에는 하나 이상의 인덱스 컬럼이나, 표헌식 등으로 구성할 수 있고, conflict_action에는 DO NOTHING 또는 DO UPDATE가 올 수 있다. 여기서 INSERT ~ ON CONFLICT DO UPDATE 구문이 바로 UPSERT에 해당한다.

예시

예로 들어 아래의 DDL로 정의된 테이블이 존재한다고 하자.

CREATE TABLE inventory
(
    id       INT PRIMARY KEY,
    name     VARCHAR(255)   NOT NULL,
    price    DECIMAL(10, 2) NOT NULL,
    quantity INT            NOT NULL
);

테이블에 데이터는 아래와 같이 저장되어 있다.

INSERT INTO inventory(id, name, price, quantity)
VALUES (1, 'A', 15.99, 100),
       (2, 'B', 25.49, 50),
       (3, 'C', 19.95, 75)
RETURNING *;

- INSERT ~ ON CONFLICT DO NOTHING

예로 들어 아래와 같이 DO NOTHING을 지정하면 지정한 컬럼에 충돌이 발생해도 아무런 일이 발생하지 않는다.

INSERT INTO inventory (id, name, price, quantity)
VALUES (4, 'D', 29.99, 20)
ON CONFLICT(id)
    DO NOTHING
;

- INSERT ~ ON CONFLICT DO UPDATE

DO UPDATE를 지정하면 지정한 컬럼에 충돌이 발생할 때 SET에서 지정한 컬럼이 갱신된다. 아래 쿼리에서 EXCLUDED란 INSERT문에서 사용한 값을 참조한다는 의미이다.

INSERT INTO inventory (id, name, price, quantity)
VALUES (3, 'D', 29.99, 20)
ON CONFLICT(id)
    DO UPDATE SET price    = EXCLUDED.price,
                  quantity = EXCLUDED.quantity
RETURNING *
;