[PostgreSQL] 데이터 있으면 UPDATE 없으면 INSERT (INSERT INTO ~ ON CONFLICT DO UPDATE)

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

오라클에서는 merge into, mysql에서는 on duplicate on key update를 사용하며 이와 비슷하게

PostgreSQL에서는 insert into ~ on conflict do 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) 특정 컬럼을 기준으로 체크하고 충돌날 경우 아무일도 하지 않기(DO NOTHING)

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 이상부터 사용 가능하다고 한다.

 

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

PostgreSQL Upsert Using INSERT ON CONFLICT statement