PostgreSQL UPSERT (UPDATE+INSERT)

UPSERT란?

UPDATE+INSERT를 합친것으로

기존 데이터가 존재하면 UPDATE해주고

기존 데이터가 존재하지 않으면 INSERT로 신규 등록해주는 기능

---

테스트 테이블 정보

-- DROP TABLE public.upsert_test_table;

CREATE TABLE public.upsert_test_table (
	sn int4 NOT NULL, -- 순번
	user_name varchar NULL, -- 데이터
	regist_dt timestamp NOT NULL, -- 등록일시
	modify_dt timestamp NULL -- 수정일시
);

-- Column comments

COMMENT ON COLUMN public.upsert_test_table.sn IS '순번';
COMMENT ON COLUMN public.upsert_test_table.user_name IS '데이터';
COMMENT ON COLUMN public.upsert_test_table.regist_dt IS '등록일시';
COMMENT ON COLUMN public.upsert_test_table.modify_dt IS '수정일시';

 

테스트 쿼리

WITH UPSERT AS ( UPDATE upsert_test_table  --update 시작
		SET	user_name = 'gitseok',
			modify_dt = now()
		WHERE sn = 1::bigint --update 끝
		RETURNING *) --update 데이터 출력(sn, user_name 등 컬럼 지정 가능)
	INSERT INTO upsert_test_table ( --insert시작
		sn,
		user_name,
		regist_dt 
	)
	SELECT
		1::bigint,
		'gitseok',
		now() --insert끝
	WHERE NOT EXISTS(SELECT sn FROM UPSERT) --row가 존재한다면 false

 

쿼리 분석

주어진 데이터를 가지고 insert문을 실행

조건문에 있는 NOT EXISTS 에 row 반환이 1이라도 있다면 false 없다면 true
    어떤 데이터의 row인가? update한 결과가 UPSERT라는 임시 테이블로 생성됨
        WITH UPSERT 내부에 있는 UPDATE문이 돌아감(동일한 sn을 가진 데이터가 있다면 업데이트)
            UPDATE 결과가 어떻게 출력되는가?
               RETURNING *(모든내용출력 원하는 컬럼들만 기입 가능 [예시 : sn, user_name]) 을 통해서

               update가 진행되었다면 해당 로우가 출력됨

-> update가 진행됐다? : row가 존재함으로 where에 false가 들어가고 insert문이 실행되지 않음
-> update가 진행되지 않았다? : row가 존재하지 않음으로 where에 true가 들어가고 insert문이 실행됨

 

쿼리 실행 결과

테스트 쿼리 실행 첫번째

sn가 1인 데이터가 없기 때문에 insert로 새로운 데이터가 추가되었다.

테스트 쿼리 실행 두번째

sn가 1인 데이터가 update 처리 되었다.

 

---

개인적으로 정리한 내용을 간단하게 풀어 작성했습니다. 이해가 안가는 부분은 댓글 남겨주시면 설명해드리겠습니다.