MERGE

소스 데이터에 기반해 테이블의 행을 조건부로 삽입(INSERT), 갱신(UPDATE), 또는 삭제(DELETE)하는 명령이에요. 단일 SQL 명령문 안에서 이 세 가지 작업을 결합할 수 있어, 그렇지 않으면 여러 개의 절차적 언어 명령문이 필요했을 작업을 하나로 처리해요. 테이블을 동기화하거나 다른 테이블에서 데이터를 채워 넣을 때 주로 사용해요.

출처: PostgreSQL 문서

본문

개요 (Synopsis)

[ WITH with_query [, ...] ]
MERGE INTO [ ONLY ] target_table_name [ * ] [ [ AS ] target_alias ]
    USING data_source ON join_condition
    when_clause [...]
    [ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]
                { * | output_expression [ [ AS ] output_name ] } [, ...] ]

where data_source is:

    { [ ONLY ] source_table_name [ * ] | ( source_query ) } [ [ AS ] source_alias ]

and when_clause is:

    { WHEN MATCHED [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } |
      WHEN NOT MATCHED BY SOURCE [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } |
      WHEN NOT MATCHED [ BY TARGET ] [ AND condition ] THEN { merge_insert | DO NOTHING } }

and merge_insert is:

    INSERT [( column_name [, ...] )]
        [ OVERRIDING { SYSTEM | USER } VALUE ]
        { VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES }

and merge_update is:

    UPDATE SET { column_name = { expression | DEFAULT } |
                 ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
                 ( column_name [, ...] ) = ( sub-SELECT )
               } [, ...]

and merge_delete is:

    DELETE

설명 (Description)

MERGE는 target_table_name으로 식별되는 대상 테이블의 행을 수정하는 동작을 data_source를 사용해 수행해요. MERGE는 행을 조건부로 INSERT, UPDATE, DELETE할 수 있는 단일 SQL 명령문을 제공하는데, 이는 그렇지 않으면 여러 개의 절차적 언어 명령문이 필요했을 작업이에요.

먼저 MERGE 명령은 data_source에서 대상 테이블로 결합(join)을 수행해 0개 이상의 후보 변경 행(candidate change row)을 생성해요. 각 후보 변경 행에 대해 MATCHED, NOT MATCHED BY SOURCE, 또는 NOT MATCHED [BY TARGET]의 상태가 정확히 한 번 설정되고, 그 후 WHEN 절이 지정된 순서로 평가돼요. 각 후보 변경 행에 대해 true로 평가되는 첫 번째 절이 실행돼요. 어떤 후보 변경 행에 대해서도 WHEN 절은 둘 이상 실행되지 않아요.

MERGE 동작은 같은 이름의 일반 UPDATE, INSERT, DELETE 명령과 같은 효과를 가져요. 이 명령들의 문법은 다릅니다. 특히 WHERE 절이 없고 테이블 이름이 지정되지 않아요. 모든 동작이 대상 테이블을 참조하지만, 다른 테이블에 대한 수정은 트리거를 사용해 수행될 수 있어요.

DO NOTHING이 지정되면 소스 행이 건너뛰어져요. 동작은 지정된 순서대로 평가되므로, DO NOTHING은 더 세밀한 처리 전에 관심 없는 소스 행을 건너뛰는 데 편리하게 쓸 수 있어요.

선택적인 RETURNING 절은 MERGE가 삽입, 갱신, 또는 삭제된 각 행을 기반으로 값(들)을 계산해 반환하도록 해요. 소스 또는 대상 테이블의 열이나 merge_action() 함수를 사용하는 어떤 표현식이든 계산될 수 있어요. 기본적으로 INSERT나 UPDATE 동작이 수행되면 대상 테이블 열의 새 값이 사용되고, DELETE가 수행되면 대상 테이블 열의 이전 값이 사용되지만, 이전 값과 새 값을 명시적으로 요청하는 것도 가능해요. RETURNING 목록의 문법은 SELECT의 출력 목록과 동일해요.

별도의 MERGE 권한은 없어요. 갱신 동작을 지정하면 SET 절에서 참조되는 대상 테이블의 열(들)에 대한 UPDATE 권한이 있어야 해요. 삽입 동작을 지정하면 대상 테이블에 대한 INSERT 권한이 있어야 해요. 삭제 동작을 지정하면 대상 테이블에 대한 DELETE 권한이 있어야 해요. DO NOTHING 동작을 지정하면 대상 테이블의 적어도 한 열에 대한 SELECT 권한이 있어야 해요. 또한 어떤 condition(join_condition 포함)이나 expression에서 참조되는 data_source와 대상 테이블의 어떤 열(들)에 대한 SELECT 권한도 필요해요. 권한은 명령문 시작 시 한 번 검사되며 특정 WHEN 절이 실행되는지 여부와 무관하게 확인돼요.

대상 테이블이 구체화된 뷰, 외부 테이블이거나, 그것에 어떤 규칙(rule)이 정의되어 있으면 MERGE는 지원되지 않아요.

파라미터 (Parameters)

*with_query* — WITH 절은 MERGE 쿼리 안에서 이름으로 참조할 수 있는 하나 이상의 서브쿼리를 지정할 수 있게 해요. 자세한 내용은 7.8절과 SELECT를 참고하세요. MERGE에서는 WITH RECURSIVE가 지원되지 않는다는 점에 유의하세요.

*target_table_name* — 병합할 대상 테이블 또는 뷰의 이름이에요(선택적으로 스키마 한정). 테이블 이름 앞에 ONLY가 지정되면 일치하는 행이 지정된 테이블에서만 갱신되거나 삭제돼요. ONLY가 지정되지 않으면 일치하는 행이 지정된 테이블에서 상속받는 모든 테이블에서도 갱신되거나 삭제돼요. 선택적으로 테이블 이름 뒤에 *를 지정해 하위 테이블이 포함됨을 명시적으로 나타낼 수 있어요. ONLY 키워드와 * 옵션은 항상 지정된 테이블에만 삽입하는 삽입 동작에는 영향을 주지 않아요.

target_table_name이 뷰라면, 그것은 INSTEAD OF 트리거가 없는 자동 갱신 가능(auto updatable)이어야 하거나, WHEN 절에 지정된 모든 유형의 동작(INSERT, UPDATE, DELETE)에 대해 INSTEAD OF 트리거가 있어야 해요. 규칙이 있는 뷰는 지원되지 않아요.

*target_alias* — 대상 테이블의 대체 이름이에요. 별칭이 제공되면 테이블의 실제 이름을 완전히 숨겨요. 예를 들어 MERGE INTO foo AS f가 주어지면 MERGE 명령문의 나머지 부분은 이 테이블을 foo가 아닌 f로 참조해야 해요.

*source_table_name* — 소스 테이블, 뷰 또는 전이 테이블(transition table)의 이름이에요(선택적으로 스키마 한정). 테이블 이름 앞에 ONLY가 지정되면 일치하는 행이 지정된 테이블에서만 포함돼요. ONLY가 지정되지 않으면 일치하는 행이 지정된 테이블에서 상속받는 모든 테이블에서도 포함돼요. 선택적으로 테이블 이름 뒤에 *를 지정해 하위 테이블이 포함됨을 명시적으로 나타낼 수 있어요.

*source_query* — 대상 테이블에 병합할 행을 공급하는 쿼리(SELECT 명령문 또는 VALUES 명령문)예요. 문법 설명은 SELECT 명령문 또는 VALUES 명령문을 참고하세요.

*source_alias* — 데이터 소스의 대체 이름이에요. 별칭이 제공되면 테이블의 실제 이름이나 쿼리가 발행되었다는 사실을 완전히 숨겨요.

*join_condition* — join_condition은 data_source의 어떤 행이 대상 테이블의 행과 일치하는지 지정하는 boolean 타입의 값을 결과로 내는 표현식이에요(WHERE 절과 비슷함).

경고 (Warning)

join_condition에는 data_source 행과 일치시키려고 시도하는 대상 테이블의 열만 나타나야 해요. 대상 테이블의 열만 참조하는 join_condition 하위 표현식은 어떤 동작이 취해질지에 영향을 줄 수 있으며, 종종 놀라운 방식으로 그래요.

WHEN NOT MATCHED BY SOURCE와 WHEN NOT MATCHED [BY TARGET] 절이 모두 지정되면 MERGE 명령은 data_source와 대상 테이블 사이에서 FULL 결합을 수행해요. 이것이 동작하려면 적어도 하나의 join_condition 하위 표현식이 해시 결합(hash join)을 지원할 수 있는 연산자를 사용해야 하거나, 모든 하위 표현식이 병합 결합(merge join)을 지원할 수 있는 연산자를 사용해야 해요.

*when_clause* — 적어도 하나의 WHEN 절이 필요해요.

WHEN 절은 WHEN MATCHED, WHEN NOT MATCHED BY SOURCE, 또는 WHEN NOT MATCHED [BY TARGET]을 지정할 수 있어요. SQL 표준은 WHEN MATCHED와 WHEN NOT MATCHED(일치하는 대상 행이 없음을 의미하는 것으로 정의됨)만 정의한다는 점에 유의하세요. WHEN NOT MATCHED BY SOURCE는 SQL 표준에 대한 확장이며, WHEN NOT MATCHED에 BY TARGET을 추가해 그 의미를 더 명시적으로 만드는 옵션도 마찬가지예요.

WHEN 절이 WHEN MATCHED를 지정하고 후보 변경 행이 data_source의 행과 대상 테이블의 행에 일치하면, condition이 없거나 true로 평가되면 WHEN 절이 실행돼요.

WHEN 절이 WHEN NOT MATCHED BY SOURCE를 지정하고 후보 변경 행이 data_source의 행과 일치하지 않는 대상 테이블의 행을 나타내면, condition이 없거나 true로 평가되면 WHEN 절이 실행돼요.

WHEN 절이 WHEN NOT MATCHED [BY TARGET]을 지정하고 후보 변경 행이 대상 테이블의 행과 일치하지 않는 data_source의 행을 나타내면, condition이 없거나 true로 평가되면 WHEN 절이 실행돼요.

*condition* — boolean 타입의 값을 반환하는 표현식이에요. WHEN 절에 대한 이 표현식이 true를 반환하면 그 행에 대해 그 절의 동작이 실행돼요.

WHEN MATCHED 절의 조건은 소스와 대상 릴레이션 양쪽의 열을 참조할 수 있어요. WHEN NOT MATCHED BY SOURCE 절의 조건은 정의상 일치하는 소스 행이 없으므로 대상 릴레이션의 열만 참조할 수 있어요. WHEN NOT MATCHED [BY TARGET] 절의 조건은 정의상 일치하는 대상 행이 없으므로 소스 릴레이션의 열만 참조할 수 있어요. 대상 테이블의 시스템 속성만 접근 가능해요.

*merge_insert* — 대상 테이블에 한 행을 삽입하는 INSERT 동작의 명세예요. 대상 열 이름은 어떤 순서로든 나열될 수 있어요. 열 이름 목록이 전혀 주어지지 않으면 기본값은 선언된 순서대로 테이블의 모든 열이에요.

명시적 또는 암시적 열 목록에 없는 각 열은 기본값으로 채워지는데, 그 열의 선언된 기본값 또는 기본값이 없으면 null로 채워져요.

대상 테이블이 분할 테이블이면 각 행이 적절한 파티션으로 라우팅되어 그 안에 삽입돼요. 대상 테이블이 파티션이라면 어떤 입력 행이든 파티션 제약을 위반할 경우 오류가 발생해요.

열 이름은 두 번 이상 지정될 수 없어요. INSERT 동작은 서브-select를 포함할 수 없어요.

VALUES 절은 하나만 지정될 수 있어요. VALUES 절은 정의상 일치하는 대상 행이 없으므로 소스 릴레이션의 열만 참조할 수 있어요.

*merge_update* — 대상 테이블의 현재 행을 갱신하는 UPDATE 동작의 명세예요. 열 이름은 두 번 이상 지정될 수 없어요.

테이블 이름이나 WHERE 절은 허용되지 않아요.

*merge_delete* — 대상 테이블의 현재 행을 삭제하는 DELETE 동작을 지정해요. DELETE 명령에서 평소처럼 하듯 테이블 이름이나 다른 어떤 절도 포함하지 마세요.

*column_name* — 대상 테이블의 열 이름이에요. 열 이름은 필요할 때 하위 필드 이름이나 배열 첨자로 한정될 수 있어요. (복합 열의 일부 필드에만 삽입하면 나머지 필드는 null로 남아요.) 대상 열의 지정에 테이블 이름을 포함하지 마세요.

OVERRIDING SYSTEM VALUE — 이 절이 없으면 GENERATED ALWAYS로 정의된 identity 열에 대해 명시적 값(DEFAULT 제외)을 지정하는 것은 오류예요. 이 절은 그 제한을 재정의해요.

OVERRIDING USER VALUE — 이 절이 지정되면 GENERATED BY DEFAULT로 정의된 identity 열에 대해 공급된 모든 값이 무시되고 기본 시퀀스 생성 값이 적용돼요.

DEFAULT VALUES — 모든 열이 기본값으로 채워져요. (이 형태에서는 OVERRIDING 절이 허용되지 않아요.)

*expression* — 열에 할당할 표현식이에요. WHEN MATCHED 절에서 사용되면 표현식이 대상 테이블의 원래 행 값과 data_source 행의 값을 사용할 수 있어요. WHEN NOT MATCHED BY SOURCE 절에서 사용되면 표현식이 대상 테이블의 원래 행 값만 사용할 수 있어요. WHEN NOT MATCHED [BY TARGET] 절에서 사용되면 표현식이 data_source 행의 값만 사용할 수 있어요.

DEFAULT — 열을 기본값으로 설정해요(특정 기본 표현식이 할당되지 않았다면 NULL이 될 거예요).

*sub-SELECT* — 그 앞의 괄호 열 목록에 나열된 만큼의 출력 열을 생성하는 SELECT 서브쿼리예요. 실행될 때 서브쿼리는 한 행 이상 생성해서는 안 돼요. 한 행을 생성하면 그 열 값이 대상 열에 할당되고, 행을 생성하지 않으면 NULL 값이 대상 열에 할당돼요. WHEN MATCHED 절에서 사용되면 서브쿼리가 대상 테이블의 원래 행 값과 data_source 행의 값을 참조할 수 있어요. WHEN NOT MATCHED BY SOURCE 절에서 사용되면 서브쿼리가 대상 테이블의 원래 행 값만 참조할 수 있어요.

*output_alias* — RETURNING 목록에서 OLD 또는 NEW 행에 대한 선택적인 대체 이름이에요.

기본적으로 대상 테이블의 이전 값은 OLD.column_name 또는 OLD.*를 써서 반환할 수 있고, 새 값은 NEW.column_name 또는 NEW.*를 써서 반환할 수 있어요. 별칭이 제공되면 이 이름들은 숨겨지고 이전 또는 새 행은 별칭을 사용해 참조해야 해요. 예를 들어 RETURNING WITH (OLD AS o, NEW AS n) o.*, n.*와 같아요.

*output_expression* — 각 행이 변경된(삽입, 갱신, 또는 삭제 여부와 무관하게) 후 MERGE 명령이 계산하고 반환하는 표현식이에요. 표현식은 소스 또는 대상 테이블의 어떤 열이든, 또는 실행된 동작에 대한 추가 정보를 반환하는 merge_action() 함수를 사용할 수 있어요.

*를 쓰면 소스 테이블의 모든 열이 반환되고, 그 뒤에 대상 테이블의 모든 열이 반환돼요. 소스와 대상 테이블이 같은 열을 많이 갖는 것이 흔하기 때문에 종종 이것은 많은 중복을 낳아요. *를 소스 또는 대상 테이블의 이름이나 별칭으로 한정하면 이를 피할 수 있어요.

열 이름이나 *는 OLD나 NEW, 또는 OLD나 NEW에 대한 해당 output_alias로 한정되어 대상 테이블의 이전 또는 새 값을 반환하게 할 수도 있어요. 대상 테이블의 한정되지 않은 열 이름, 또는 대상 테이블 이름이나 별칭으로 한정된 열 이름이나 *는 INSERT와 UPDATE 동작에 대해서는 새 값을, DELETE 동작에 대해서는 이전 값을 반환해요.

*output_name* — 반환된 열에 사용할 이름이에요.

출력 (Outputs)

성공적으로 완료되면 MERGE 명령은 다음 형태의 명령 태그를 반환해요.

MERGE total_count

total_count는 변경된(삽입, 갱신, 또는 삭제 여부와 무관하게) 총 행 수예요. total_count가 0이면 어떤 방식으로도 변경된 행이 없다는 뜻이에요.

MERGE 명령에 RETURNING 절이 포함되어 있으면, 결과는 RETURNING 목록에 정의된 열과 값을 포함하는 SELECT 명령문의 결과와 비슷해요. 명령에 의해 삽입, 갱신, 또는 삭제된 행(들)을 대상으로 계산돼요.

참고 (Notes)

MERGE 실행 동안 다음 단계들이 일어나요.

지정된 모든 동작에 대해 BEFORE STATEMENT 트리거를 수행해요. 그 WHEN 절이 일치하는지 여부와 무관해요.
소스에서 대상 테이블로 결합을 수행해요. 결과 쿼리는 정상적으로 최적화되며 후보 변경 행 집합을 생성해요. 각 후보 변경 행에 대해
- 각 행이 MATCHED, NOT MATCHED BY SOURCE, 또는 NOT MATCHED [BY TARGET]인지 평가해요.
- 하나가 true를 반환할 때까지 각 WHEN 조건을 지정된 순서대로 검사해요.
- 조건이 true를 반환하면 다음 동작들을 수행해요:
  - 동작의 이벤트 유형에 대해 발생하는 BEFORE ROW 트리거를 수행해요.
  - 지정된 동작을 수행하고, 대상 테이블의 모든 검사 제약(check constraint)을 호출해요.
  - 동작의 이벤트 유형에 대해 발생하는 AFTER ROW 트리거를 수행해요.
  - 대상 릴레이션이 동작의 이벤트 유형에 대한 INSTEAD OF ROW 트리거가 있는 뷰라면, 그것들이 대신 그 동작을 수행하는 데 사용돼요.
지정된 동작에 대해 AFTER STATEMENT 트리거를 수행해요. 그것이 실제로 발생하는지 여부와 무관해요. 이것은 행을 수정하지 않는 UPDATE 명령문의 동작과 비슷해요.

요약하면, 어떤 이벤트 유형(예: INSERT)에 대한 명령문 트리거는 그런 종류의 동작을 지정할 때마다 발생해요. 반대로 행 수준 트리거는 실행되는 특정 이벤트 유형에 대해서만 발생해요. 그래서 MERGE 명령은 UPDATE 행 트리거만 발생했더라도 UPDATE와 INSERT 양쪽에 대한 명령문 트리거를 발생시킬 수 있어요.

결합이 각 대상 행에 대해 기껏 하나의 후보 변경 행을 생성하도록 보장해야 해요. 다시 말해, 대상 행이 둘 이상의 데이터 소스 행에 결합해서는 안 돼요. 그렇게 되면 후보 변경 행 중 하나만 대상 행을 수정하는 데 사용되고, 그 행을 수정하려는 이후 시도는 오류를 일으켜요. 행 트리거가 대상 테이블을 변경하고 그렇게 수정된 행이 이후 MERGE에 의해서도 수정되면 이것도 발생할 수 있어요. 반복된 동작이 INSERT라면 이것은 고유성 위반을 일으키고, 반복된 UPDATE나 DELETE는 카디널리티 위반을 일으켜요. 후자의 동작은 SQL 표준에서 요구돼요. 이것은 같은 행을 수정하려는 두 번째 및 이후 시도가 그냥 무시되는 역사적인 PostgreSQL UPDATE와 DELETE 명령문의 결합 동작과 다릅니다.

WHEN 절이 AND 하위 절을 생략하면 그것은 그 종류(MATCHED, NOT MATCHED BY SOURCE, 또는 NOT MATCHED [BY TARGET])의 마지막 도달 가능한 절이 돼요. 그 종류의 이후 WHEN 절이 지정되면 그것은 입증 가능하게 도달할 수 없으며 오류가 발생해요. 어떤 종류의 마지막 도달 가능한 절도 지정되지 않으면 후보 변경 행에 대해 어떤 동작도 취해지지 않을 수 있어요.

데이터 소스에서 행이 생성되는 순서는 기본적으로 불확정적(indeterminate)이에요. 필요하다면 source_query를 사용해 일관된 순서를 지정할 수 있는데, 이는 동시 트랜잭션 사이의 교착 상태를 피하기 위해 필요할 수 있어요.

MERGE가 대상 테이블을 수정하는 다른 명령과 동시에 실행될 때 일반적인 트랜잭션 격리 규칙이 적용돼요. 각 격리 수준에서의 동작에 대한 설명은 13.2절을 참고하세요. 동시 INSERT가 발생할 때 UPDATE를 실행할 수 있는 기능을 제공하는 대안 명령문으로 INSERT ... ON CONFLICT를 고려해 볼 수도 있어요. 두 명령문 유형 사이에는 다양한 차이점과 제한이 있으며 서로 바꿔 쓸 수 없어요.

예제 (Examples)

새 recent_transactions를 기반으로 customer_accounts에 대한 유지보수를 수행하려면.

MERGE INTO customer_account ca
USING recent_transactions t
ON t.customer_id = ca.customer_id
WHEN MATCHED THEN
  UPDATE SET balance = balance + transaction_value
WHEN NOT MATCHED THEN
  INSERT (customer_id, balance)
  VALUES (t.customer_id, t.transaction_value);

새 재고 품목과 재고 수량을 삽입하려고 시도하려면. 품목이 이미 존재하면 기존 품목의 재고 수를 대신 갱신해요. 재고가 0인 항목은 허용하지 마세요. 이루어진 모든 변경 사항에 대한 세부 정보를 반환하려면.

MERGE INTO wines w
USING wine_stock_changes s
ON s.winename = w.winename
WHEN NOT MATCHED AND s.stock_delta > 0 THEN
  INSERT VALUES(s.winename, s.stock_delta)
WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN
  UPDATE SET stock = w.stock + s.stock_delta
WHEN MATCHED THEN
  DELETE
RETURNING merge_action(), w.winename, old.stock AS old_stock, new.stock AS new_stock;

wine_stock_changes 테이블은 예를 들어 최근에 데이터베이스에 로드된 임시 테이블일 수 있어요.

대체 와인 목록을 기반으로 wines를 갱신하려면. 새 재고에 대해 행을 삽입하고, 수정된 재고 항목을 갱신하며, 새 목록에 없는 와인은 삭제해요.

MERGE INTO wines w
USING new_wine_list s
ON s.winename = w.winename
WHEN NOT MATCHED BY TARGET THEN
  INSERT VALUES(s.winename, s.stock)
WHEN MATCHED AND w.stock != s.stock THEN
  UPDATE SET stock = s.stock
WHEN NOT MATCHED BY SOURCE THEN
  DELETE;

호환성 (Compatibility)

이 명령은 SQL 표준을 따릅니다.

WITH 절, WHEN NOT MATCHED에 대한 BY SOURCE와 BY TARGET 한정자, DO NOTHING 동작, 그리고 RETURNING 절은 SQL 표준에 대한 확장이에요.

더 알아보기 (Learn more)

단일 행의 삽입·갱신을 ON CONFLICT로 다루는 방법은 INSERT 문서를, 트리거와 데이터 변경 규칙은 "Triggers" 절을 함께 보면 좋아요.