[PostgreSQL] insert 혹은 update upsert - ON CONFLICT 사용 방법

ON CONFLICT 구문은 PostgreSQL의 INSERT 문에서 중복 충돌을 관리하는 옵션입니다. ON CONFLICT는 일반적으로 데이터를 삽입할 때 기본 키나 고유 제약 조건이 위반되는 경우의 처리를 제어하는 데 유용하며, 데이터베이스에 존재하는 레코드와 삽입할 레코드 간의 중복이 발생할 때 다양한 행동을 지정할 수 있습니다.

---

ON CONFLICT의 주요 구문

기본 구문은 다음과 같습니다:

INSERT INTO 테이블명 (열1, 열2, ...)
VALUES (값1, 값2, ...)
ON CONFLICT (열)
DO UPDATE SET 열1 = 값, 열2 = 값

 

이 구문에서:

• ON CONFLICT (열)은 충돌을 감지할 열을 지정합니다.
• DO UPDATE SET은 중복된 값이 있을 때 업데이트할 열과 새 값을 설정합니다.
• DO NOTHING을 사용하면 충돌 시 아무 작업도 수행하지 않고 넘어갑니다.

---

예제 1: 기본적인 ON CONFLICT 사용

상품 정보를 관리하는 products 테이블에 중복된 상품이 추가될 경우 가격을 업데이트하는 예제를 보겠습니다.

테이블 생성 및 샘플 데이터 삽입

먼저, products 테이블을 생성하고 기본 데이터를 삽입합니다.

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name TEXT UNIQUE,
    price NUMERIC
);

INSERT INTO products (product_name, price)
VALUES ('Smartphone', 500),
       ('Laptop', 1500),
       ('Tablet', 800);

 

ON CONFLICT를 사용한 업데이트 예제

새로운 데이터 삽입 시 product_name이 중복되는 경우 가격을 업데이트하도록 설정합니다.

INSERT INTO products (product_name, price)
VALUES ('Smartphone', 550) 
ON CONFLICT (product_name) 
DO UPDATE SET price = EXCLUDED.price;

 

위 예제에서 ON CONFLICT (product_name)으로 product_name 열에서 중복된 값이 있을 경우를 지정하고, DO UPDATE SET을 통해 price 열의 값을 EXCLUDED.price로 업데이트했습니다. EXCLUDED는 PostgreSQL에서 제공하는 키워드로, 중복으로 인해 제외된 새로운 값을 의미합니다.

이제 products 테이블은 아래와 같이 업데이트됩니다:

product_id	product_name	price
1	Smartphone	550
2	Laptop	1500
3	Tablet	800

 

예제 2: ON CONFLICT DO NOTHING 사용

기존에 데이터가 존재하면 아무 작업도 수행하지 않도록 할 수 있습니다.

INSERT INTO products (product_name, price)
VALUES ('Smartphone', 600)
ON CONFLICT (product_name) 
DO NOTHING;

 

이 구문은 product_name이 중복될 경우 아무 작업도 수행하지 않습니다. 따라서 price는 기존의 550으로 유지됩니다.

---

예제 3: 여러 열을 기준으로 ON CONFLICT 사용

여러 열의 조합이 고유한 경우, 해당 열들에 대해 충돌 처리를 할 수도 있습니다.

예를 들어, sales 테이블에서 특정 상품과 고객 ID의 조합이 중복되는 경우 수량을 업데이트하는 예제입니다.

CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    product_id INTEGER,
    customer_id INTEGER,
    quantity INTEGER,
    UNIQUE (product_id, customer_id)
);

 

여기서 product_id와 customer_id가 고유 제약 조건을 가지며, 중복된 경우 quantity를 업데이트합니다.

INSERT INTO sales (product_id, customer_id, quantity)
VALUES (1, 1, 10)
ON CONFLICT (product_id, customer_id)
DO UPDATE SET quantity = sales.quantity + EXCLUDED.quantity;

 

위 구문은 product_id와 customer_id 조합이 중복될 때 quantity를 기존 값에 더해 업데이트합니다.

---

에러가 발생할 수 있는 경우

ON CONFLICT 구문을 사용할 때 발생할 수 있는 주요 에러는 다음과 같습니다:

1. 대상 열이 고유 제약 조건 또는 기본 키가 아닌 경우

ON CONFLICT는 중복 충돌을 탐지하기 위해 고유 제약 조건이 있는 열을 필요로 합니다. 만약 충돌 처리 대상 열에 고유 제약 조건이 없으면 다음과 같은 오류가 발생합니다:

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

 

해결 방법: 충돌 처리 대상 열에 고유 제약 조건을 추가하거나 기본 키로 설정합니다.

ALTER TABLE products ADD CONSTRAINT unique_product_name UNIQUE (product_name);

 

2.   충돌 처리 대상 열이 두 개 이상이고, 명시적 조합이 지정되지 않은 경우

여러 개의 열이 고유 제약 조건을 가지는 테이블에서 ON CONFLICT 열을 지정하지 않으면 PostgreSQL이 중복 기준을 혼동할 수 있습니다.

해결 방법: 여러 열에 대한 고유 제약 조건이 있는 경우 (열1, 열2)와 같이 구체적으로 지정해야 합니다.

INSERT INTO sales (product_id, customer_id, quantity)
VALUES (1, 1, 10)
ON CONFLICT (product_id, customer_id)
DO UPDATE SET quantity = sales.quantity + EXCLUDED.quantity;

 

3.   타입 불일치 오류

업데이트할 열의 타입이 입력된 데이터와 다르면 충돌 시 업데이트가 실패합니다.

INSERT INTO products (product_name, price)
VALUES ('Smartphone', '오백') -- price는 숫자형이어야 합니다.
ON CONFLICT (product_name)
DO UPDATE SET price = EXCLUDED.price;

 

1. 위와 같은 경우, 문자열이 숫자형 price 열에 맞지 않아 오류가 발생합니다.

---

 

ON CONFLICT는 PostgreSQL에서 충돌이 발생할 때 유연하게 데이터를 삽입하거나 업데이트하는 기능을 제공합니다. 특히, 데이터를 덮어쓰거나 특정 열을 기준으로 업데이트하는 다양한 시나리오에 적합합니다. 다만, 고유 제약 조건을 설정해야 하며, 잘못된 데이터 타입이나 잘못된 열 지정으로 인해 오류가 발생할 수 있음을 주의해야 합니다.