PostgreSQL Upsert(Update&Insert) 사용

PostgreSQL를 사용해서 upsert를 실행하는 문법을 알아보자. upsert는 Insert 시 pk가 중복 등으로 Insert 하려는 값이 존재하는 경우 해당 row를 update하는 쿼리이다.

 

먼저 문법을 보면 아래와 같다.

INSERT INTO  // insert문
ON CONFLICT  // 충돌 체크
DO UPDATE    // update문

 

문법을 기준으로 쿼리를 작성해보면 아래와 같은 예시가 된다. USER테이블에 insert문을 실행하는데 'id' 컬럼에 해당하는 row가 없으면 insert, 이미 존재하면 age에 +1을 한 값으로 update한다.

INSERT INTO USER (id, name , age) 
VALUES('user1', 'Hong', 1)

ON CONFLICT (id)

DO UPDATE
SET age = USER.age + 1;

 

위의 구문을 활용하면 멀티 upsert도 가능하다

INSERT INTO USER (id, name , age) 
VALUES('user1', 'Hong', 1), ('user2', 'kim', 2)

ON CONFLICT (id)

DO UPDATE
SET age = USER.age + 1;

 

EXCLUDED를 사용하여 update 시에 insert 구문에서 사용했던 값(value)을 그대로 사용가능하다.  'id' 컬럼에 해당하는 row가 이미 존재하여 update 시 age를 1(insert구문에서의 age값)로 update 한다.

INSERT INTO USER (id, name , age) 
VALUES('user1', 'Hong', 1)

ON CONFLICT (id)

DO UPDATE
SET age = EXCLUDED.age;

 

마지막으로 아래와 같이 select upsert문도 가능하다. insert문에서 values구문 대시 select문을 사용하고, update 구문을 처리할때는 select문의 조회된 결과값으로 update를 실행한다.

INSERT INTO USER (id, name , age) 
    SELECT id, name, age 
    FROM SRC_USER
    WHERE age >= 10

ON CONFLICT (id)

DO UPDATE
    SET 
        name = EXCLUDED.name, 
        age = EXCLUDED.age;