[PostgreSQL] Upsert 구문(INSERT … ON CONFLICT)

소스 코드를 분석하면서 insert 문과 관련된 처음보는 문법을 보았다.

동작 자체는 어렵지 않지만 update문과 다른 의미로 유용하게 쓰였기 때문에 정리를 하려 한다.

(실시간으로 데이터가 들어오기 때문에 매번 데이터를 찾아 update를 실행하기 보다는 일단 insert를 하고 unique한 값이 동일하면 특정 데이터를 update 하는 것이 더 효율적이라 생각한다.)

단, ON CONFLICT는 PostgreSQL 9.5 이상의 버전에서만 사용이 가능하다.

기본적인 사용 형태는 아래와 같다.

INSERT INTO table_name(column_list)
VALUES(value_list)
ON CONFLICT target action;

target은 다음 중 하나로 사용이 가능하다.

• (column_name) : 특정 column의 값을 기준으로 체크
• ON CONSTRAINT constraint_name : constraint 기준으로 체크
• WHERE predicate : WHERE OPTION으로 UNIQUE INDEX 생성 시 사용

action에서는 다음과 같은 구문을 사용할 수 있다.

• DO NOTHING : 기존에 존재하는 row가 있는 경우 아무런 동작을 하지 않는다.
• DO UPDATE SET column_1 = value_1, .. WHERE condition : 기존 row를 update 한다.

 

PostgreSQL의 공식 페이지에서의 예시를 통해 구문을 어떻게 적용할 수 있는지 알아보자.

CREATE TABLE customers (
	customer_id serial PRIMARY KEY,
    name VARCHAR UNIQUE,
    email VARCHAR NOT NULL,
    status INTERGER DEFAULT 0 NOT NULL
);

INSERT INTO
	customers (name, email)
VALUES
	('IBM', 'contact@ibm.com'),
    ('Microsoft', 'contact@microsoft.com'),
    ('Intel', 'contact@intel.com');

1. 특정 컬럼을 기준으로 체크하고 충돌이 날 경우 아무 일도 하지 않기(DO NOTHING)

INSERT INTO customers (name, email)
VALUES(
	'Microsoft',
    'hotline@microsoft.com'
) 
ON CONFLICT (name) 
DO NOTHING;

customers 테이블에서 name이 중복되는 경우 아무런 동작을 수행하지 않는다.

DO NOTHING을 사용하면 unique한 값을 갖는 primary key에 대한 중복 삽입이 일어날 때 error가 발생하지 않는다.

2. 특정 CONSTRAINT 기준으로 체크

INSERT INTO customers (name, email)
VALUES(
	'Microsoft',
    'hotline@microsoft.com'
) 
ON CONFLICT ON CONSTRAINT customers_name_key 
DO NOTHING;

3. 특정 컬럼을 기준으로 체크하고 충돌날 경우 UPDATE하기

INSERT INTO customers (name, email)
VALUES(
	'Microsoft',
    'hotline@microsoft.com'
) 
ON CONFLICT (name) 
DO UPDATE SET email = EXCLUDED.email || ';' || customers.email;

 3번 방식이 내가 소스코드를 공부하면서 봤던 형식이다.

name이 중복될 경우, 뒤에 오는 DO UPDATE SET을 통해 데이터를 UPDATE 할 수 있다.

 

 

 

---

[ 참고 ]

https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-upsert/

https://sungtae-kim.tistory.com/36