오늘은, 아래와 같은 상황에서, 키가 중복해서 들어가 있다면, update를, 그렇지 않으면 insert를 하는 예제를 보도록 하겠습니다. 당연하게도, 이 문서를 같이 읽어보시는 것을 권해드립니다.

 


 기존 테이블은 오래된 데이터입니다. 몇 데이터가 들어오는데, 이 중에는 '업데이트'가 된 데이터도 있습니다. 예를 들자면, id에 대해서. 그러한 경우에 어떻게 해야 할까요? postgresql에는 upsert가 있다고 하는데.. 문제부터 보겠습니다.

 

 

 sampleT는 아래와 같습니다. id와 name으로 정의가 되어 있고, id는 primary key입니다.

 

 

 sampleT에 추가되어 있는 데이터를 보도록 하겠습니다. select from을 이용하면 매우 간단하게 조회할 수 있습니다.

 

 

 1, 'cho'하고, 2, 'ga'하고 3, 'hui'가 조회됩니다.

 

 

 다음에, (2, 'mal'), (4,'ti'), (6,'ze')를 추가하려고 합니다. 이렇게 입력하고 추가해 봅시다.

 

 

 그러면 왠 에러가 나옵니다. 이는 id가 2인 것이 이미 있기 때문입니다.

 


 그러면 어떻게 해야 할까요? 아래와 같이 쿼리문을 작성해 주시면 됩니다.

 

 이것은, (2, 'mal'), (4, 'ti'), (6, 'ze')를 넣는데, 만약에 기존에 키가 있다면, 업데이트를 합니다. 5번째 줄이 그러한 일을 수행합니다. 여기서 new_recode.name은 키가 중복되어 있다면, 새롭게 업데이트를 할 값을 의미합니다.

 

 

 

 일단 잘 들어간 듯 보입니다. 중요한 것은 unique나 primary key가 하나만 있었다는 겁니다. 여러 개가 있다면 어떻게 동작할까요?

 

 sampleT2는 위와 같습니다.

 

 그리고 위 쿼리를 작성해서 실행을 하였습니다.

 

 

 그랬더니 이해할 수 없는 결과가 나왔는데, 사실 이는 unique 키 2개가 들어갔습니다. id1과 id2. 우리가 레코드를 넣을 건데요. 이미 있는 레코드를 old라고 하고, 새롭게 추가될 레코드를 new라고 해 봅시다. old.id1과 new.id1이 같거나, 아니면 old.id2와 new.id2가 같다면 업데이트가 되게끔 들어갔기 때문일 겁니다. 정말 그런지, 하나의 쿼리를 더 넣어보겠습니다.

 

 

 id1이 1이고, id2가 3이고, name이 당하면 화가 나는 'SYSTEM TEST FAILED 120'을 넣습니다. 결과가 어찌 나올까요?

 

 

 하나만 업데이트 됩니다. 분명히, 2개 이상이 match가 되는 상황임에도 하나만 업데이트가 되었는데, 이는 공식 문서에서 only one row updated 라는 문구를 보시면 알 수 있습니다. 그리고, 이런 내용도 나와 있습니다. multiple unique index가 걸려 있다면 이 문장을 사용하는 걸 피해라.


 번외편. 해당 키가 있으면 값을 넣지도, 업데이트도 하지 않게 하려면 어떻게 해야 할까요? SQL 연습 사이트에서 많이 본 거 같은데..

 

 

 일단 넣을 데이터를 view table로 만들어 보겠습니다.

 

 

 그럴려면, 적당히 select 절과 union을 잘 섞어 주면 됩니다. 이 결과를 쿼리문의 내부 view로 만듭니다.

 

 

 다음에, insert_recode랑, sampleT를 outer join을 합니다.

 

 

 그러면 이렇게 2개의 결과값이 나옵니다. 이들을 insert 구문에 넣어주면 됩니다.

 

 

 최종적으로 결과는 아래와 같이 나옵니다.

 

 

 이 쿼리를 좀 더 간단히 하는 것은 고민해 봅시다.