[PostgreSQL] UPSERT

관계형데이터베이스에서 upsert는 'merge'라는 용어로 사용되기도 한다.

 

PostgreSQL에서 새로운 행을 테이블에 추가하려고 할 때, 시스템은 해당 행이 이미 테이블에 존재하는지 확인한다.

이미 존재한다면, 새로운 데이터로 해당 행을 업데이트하고, 행이 존재하지 않는 경우에는 새로운 데이터를 포함하는 행을 테이블에 추가한다.

그래서 우리가 이 동작을 upsert라고 부르는 이유다. (update와 insert의 결합)

 

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

 

PostgreSQL에서는 upsert를 지원해주기 때문에, INSERT문에 ON CONFLICT target action 을 추가하면 된다.

INSERT 문으로 데이터를 삽입하려고 할 때, 이미 테이블에 중복된 데이터가 존재할 경우 어떻게 처리해야 하는지를 지정해주는 부분이다.

 

INSERT INTO table_name(column_list)
VALUES (value_list)
    ON CONFLICT DO NOTHING;

 

ON CONFLICT DO NOTHING 이라고 되어있으면 중복되는 데이터가 존재하는 경우 아무것도 하지 않고 끝이 난다.

만약 update 작업을 수행하고 싶다면,

 

INSERT INTO table_name(column_list)
VALUES (value_list)
    ON CONFLICT
UPDATE SET
	   column_1 = value_1,
       ...
 WHERE condition;

 

ON CONFLICT UPDATE SET ~ 를 붙여주면 된다.

 

아래는 upsert를 해보기위한 예제 소스다.

 

DROP TABLE IF EXISTS restaurants;

CREATE TABLE restaurants (
	restaurants_no serial PRIMARY KEY,
    	name VARCHAR UNIQUE,
    	address VARCHAR NOT NULL,
    	active bool NOT NULL DEFAULT TRUE
);

 

restaurants 테이블은 4개의 컬럼으로 이루어져있다.

restaurants_no, name, address 그리고 active이다.

여기서 name은 식당 이름의 고유성을 보장하기 위해서 고유 제약(unique constraint)이 걸려있다.

 

INSERT INTO restaurants (name, address)
VALUES
	('서브웨이', '엠스테이트 빌딩'),
    	('메이', 'H비즈니스파크'),
        ('요커', '현대지식산업센터');

 

restaurants 테이블에 3개의 데이터를 추가했다.

	restaurant_no	name	address	active
1	1	서브웨이	엠스테이트 빌딩	true
2	2	메이	H비즈니스파크	true
3	3	요커	현대지식산업센터	true

 

만약 서브웨이가 주소를 H비즈니스파크에서 송파한화오벨리스크로 바꾼다면, UPDATE 구문을 이용해서 할수도 있지만

UPSERT를 이용해서 작업을 해보자.

 

INSERT INTO restaurants (NAME, address)
VALUES ('서브웨이', '송파한화오벨리스크')
    ON CONFLICT ON CONSTRAINT restaurants_name_key
    DO NOTHING;
    
INSERT INTO restaurants (name, address)
VALUES ('서브웨이', '송파한화오벨리스크')
    ON CONFLICT (name)
    DO NOTHING;

 

첫번째 구문은 고유제약을 이용한 방법이고, 두번째 구문은 name 컬럼을 사용한 것이다. 두개의 구문은 동일한 기능을 수행한다.

 

INSERT INTO restaurants (name, address)
VALUES ('서브웨이', '송파한화오벨리스크')
    ON CONFLICT (name)
    DO
UPDATE
   SET address = EXCLUDED.address || ',' || restaurants.address;

 

	restaurant_no	name	address	active
1	1	서브웨이	엠스테이트 빌딩,송파한화오벨리스크	true
2	2	메이	H비즈니스파크	true
3	3	요커	현대지식산업센터	true