[PostgreSQL] 데이터 있으면 update 없으면 insert

PostgreSQL 에서 사용하는 upsert구문에 대해 알아보자.

 

(오라클에서는 merge into / mysql에서는 on duplicate on key update 를 사용한다.)

 

구문은 다음과 같다.

 

INSERT INTO [TABLE] (COLUMN1, COLUMN2, ...)

VALUES (VALUE1, VALUE2, ...)

ON CONFLICT [column_name / ON CONSTRAINT constraint_name/ WHERE predicate]

[DO NOTHING] 

[DO UPDATE SET column1 = value1, ...]

 

기본구문은 이런식으로 이루어져 있고 하나씩 설명하자면

 

ON CONFLICT column_name : 특정 컬럼명을 기준으로 체크를하며 컬럼을 여러개 넣을수도 있다.(pk기준)

ON CONFLICT ON CONSTRAINT ~ : 테이블 생성시 만든 constraint명을 기준으로 체크한다.

ON CONFLICT WHERE predicate : UNIQUE INDEX 생성 시 사용(안써봤다.)

 

특정 기준으로 ON CONFLICT에 걸릴 경우

 

DO NOTHING : 아무런 동작을 안하도록 설정

DO UPDATE SET ~ : 기존에 쓰던 UPDATE의 역할 가능 

 

이제 예제를 통해 알아보자.

---

( 1 ) 특정 컬럼을 기준으로 체크하고 충돌날 경우 넘어가기

(아무일 발생 안함)

 

INSERT INTO EMPLOYEE (EMP_SN, SALARY, NAME, DEPT, ETC)

VALUES (1, 5000, 'mine', 'it', 'record')

ON CONFLICT (EMP_SN)

DO NOTHING

 

( 2 ) 특정 컬럼을 기준으로 체크하고 충돌날 경우 UPDATE 하기

 

INSERT INTO EMPLOYEE (EMP_SN, SALARY, NAME, DEPT, ETC)

VALUES (#{empSn}, #{salary}, #{name}, #{dept}, #{etc})

ON CONFLICT (EMP_SN)

DO UPDATE

SET EMP_SN = #{empSn},

SALARY  = #{salary},

NAME    = #{name},

DEPT     = #{dept},

ETC       = #{etc}

 

( 3 ) 특정 constraint 기준으로 체크

 

INSERT INTO EMPLOYEE (EMP_SN, SALARY, NAME, DEPT, ETC)

VALUES (2, 2400, 'mine', 'it', 'record')

ON CONFLICT ON CONSTRAINT mine_it_record_key

DO NOTHING

---

해당 upsert 구문은 PostgreSQL 9.5 이상부터 사용 가능하다고 한다.

 

출처: https://mine-it-record.tistory.com/342 [나만의 기록들:티스토리]

 

참고 : http://www.postgresqltutorial.com/postgresql-upsert