[Postgresql] Upsert 구현 (insert ~ on conflict)

DB 구현시 많이 쓰는 패턴중에 하나가 해당 row가 있으면 update, 없으면 insert를 하는 패턴일 것이다. Oracle의 경우는 merge ~ into 구문을 통해 한번의 명령으로 해당 로직을 구현할 수 있다. Postgres에서도 동일한 명령이 있지 않을까?

 

아래와 같이 insert ~ on conflict 구문을 활용하면 구현할 수 있다.

 

문법은 다음과 같다.

INSERT INTO [TABLE] (COLUMN1, COLUMN2, ...)
     VALUES (VALUE1, VALUE2, ...)
     ON CONFLICT ([column_name / ON CONSTRAINT constraint_name/ WHERE predicate])
     [DO NOTHING]
     [DO UPDATE SET column1 = value1, ...]

 

ON CONFLICT column_name : 특정 컬럼명을 기준으로 체크를하며 컬럼을 여러개 넣을수도 있다.(pk기준)
ON CONFLICT ON CONSTRAINT ~ : 테이블 생성시 만든 constraint명을 기준으로 체크한다.
ON CONFLICT WHERE predicate : UNIQUE INDEX 생성 시 사용.

 

action 에서는 다음과 같은 구문을 사용할 수 있다.
- DO NOTHING : 기존에 존재하는 row가 있는 경우 아무런 동작을 하지 않는다.
- DO UPDATE SET column_1 = value_1, .. WHERE condition : 기존 row를 update 한다.

 

단일 row upsert

 

아래와 같이 테스트 용 테이블과 초기데이터가 있다고 해보자.

CREATE TABLE users (
    user_id serial PRIMARY KEY,
    NAME VARCHAR(20)
)

SELECT * FROM users ORDER BY user_id asc;

 

on conflict를 이용한 insert 테스트

-- id 6에 대해서 conflict가 발생하지만 아무것도 안함
insert into users (user_id, name) VALUES (6, '홍길동2') ON CONFLICT (user_id) DO NOTHING;

-- id 6에 대해서 conflict가 발생하고 해당 row의 name을 '홍길동2' 로 업데이트 함
insert into users (user_id, name) VALUES (6, '홍길동2') ON CONFLICT (user_id) DO UPDATE SET NAME = '홍길동3'

 

위 upsert를 수행한 결과 6번 id의 name값이 홍길동3로 바뀌었음을 알 수 있다.

conflict 에 지정한 컬럼(단 여기에 나오는 컬럼은 pk 로 사용되는 컬럼 이어야 함)의 충돌이 있고 DO UPDATE를 명시하였을 경우 해당 row가 업데이트 됨을 볼 수 있다. 

 

Multiple row upsert

만일 multiple row insert 시에도 동작을 제대로 할까? 답은 동일하게 동작한다 이다. 단 on conflict 절에서 충돌난 row의 update 지정 시 insert 시에 사용한 값을 활용하기 위해 excluded.변수명 형식으로 지정해 주면 된다. 

 

현재 초기 값은 다음과 같다고 가정하고

-- insert 구문시의 변수값을 그대로 활용하려면 DO UPDATE절에 excluded 키워드를 붙여준다.
insert into users (user_id, name) VALUES (6, '홍길동4'), (8, '컴퓨터4') ON CONFLICT (user_id) DO UPDATE SET NAME = excluded.name;

 

결과는 다음과 같다. id 출돌이 난 6,8번 row에 대해서 각각의 insert 시 값으로 갱신되어 있음을 알 수 있다.

 

-- The end --