Alchemine Studio

Upsert

 
  • Aug 10, 2023

DB마다 insert 시 충돌이 나는 경우를 처리해주는 upsert 구현에서 차이가 많이 난다.

Example

같은 scheme을 사용하는 테이블 table_a, table_b가 주어졌을 때, table_b의 값을 table_a로 upsert하는 SQL을 살펴보자.
SQL OnLine IDE에서 실습이 가능하다!

  • id가 같은 경우, INT column은 더하고, DATE column은 더 최근값으로 업데이트
CREATE TABLE
    table_a (
        id   INT    PRIMARY KEY,
        a    INT,
        b    INT,
        date DATE);

INSERT INTO
    table_a
VALUES
    (1, 1, 2, '2020-01-01'),
    (2, 3, 4, '2020-02-01'),
    (3, 5, 6, '2020-03-01');

CREATE TABLE
    table_b (
        id   INT    PRIMARY KEY,
        a    INT,
        b    INT,
        date DATE);

INSERT INTO
    table_b
VALUES
    (3, 10, 20, '2020-04-01'),
    (4, 30, 40, '2020-05-01');

Input

  • table_a

    id a b date
    1 1 2 2020-01-01
    2 3 4 2020-02-01
    3 5 6 2020-03-01

  • table_a

    id a b date
    3 10 20 2020-04-01
    4 30 40 2020-05-01

Output

  • table_a

    id a b date
    1 1 2 2020-01-01
    2 3 4 2020-02-01
    3 15 26 2020-04-01
    4 30 40 2020-05-01

1. PostgreSQL

INSERT INTO
    table_a (id, a, b, date)
SELECT
    b.id,
    b.a,
    b.b,
    b.date
FROM
    table_b AS b

ON CONFLICT
    (id)
DO UPDATE SET
    a    = table_a.a + EXCLUDED.a,
    b    = table_a.b + EXCLUDED.b,
    date = GREATEST(table_a.date, EXCLUDED.date);
  1. ON CONFLICT 구문은 PostgreSQL 9.5 이상의 버전에서만 지원됨
  2. 일반적으로 사용되는 ON CONFLICT 구문은 다음과 같은 형태를 가진다. (doc) 
     INSERT INTO
     table_name
 VALUES
     (...)
 ON CONFLICT
     [conflict_target]
 conflict_action
    1. conflict_target
      1. (columns)
        columns를 기준으로 중복 체크 (위 예시)
      2. ON CONSTRAINT constraint
        constraint를 기준으로 중복 체크
    2. conflict_action
      1. DO NOTHING
        아무 것도 하지 않음
      2. DO UPDATE SET
        조건에 해당되는 column을 업데이트 (위 예시)
  3. 관련된 몇 가지 예시들은 다음 링크를 참고
    https://dbfiddle.uk/l0dNf3hO

References