[postgresql] with절로 insert,update,delete 를 활용

WITH 절은 다양한 서브쿼리를 한 곳에 정의하며 가독성도 높이고 재사용성이 좋아 사용하게 되는데

 

예를 들어 배치 스케쥴러 특정 시간 마다 데이터가 있으면 UPDATE , 없으면 INSERT 해주는 쿼리를 작성해야 할 때

 

WITH절을 사용해서 하나의 쿼리로 완성할 수 있다.

 

WITH UPSET AS(
     UPDATE TEST_TABLE
     SET 컬럼1 = '테스트'
     WHERE 기본키컬럼 = '기본키데이터'
     RETURNING *
)
INSERT INTO TEST_TABLE

(기본키컬럼,컬럼1,컬럼2) 
SELECT  '기본키데이터',NULL,NULL
WHERE NOT  EXISTS (SELECT * FROM UPSET )
--RETURNING *

 

따로 VALUES를 쓰지 않고 SELECT하면서 #{기본키컬럼VO} 하면서 알맞게 데이터를 갖고오면된다.

RETURNING은 해당 작업 완료 후 ROW 결과값을 반환해준다.

WHERE NOT EXISTS (SELECT * FROM UPSET)에서 WITH절에  RETURNING * 반환된 데이터가 없으면

NOT EXISTS 즉 INSERT문을 하게된다. 

데이터가 있다면 UPDATE를 하고 반환 후 NOT EXISTS로 INSERT문은 하지 않는다.

 

POSTGRESQL DBMS만 문제인지 모르지만 RETURNING을 쓰지 않으면 

WITH query "upset" does not have a RETURNING clause

오류가 뜰 것이다.

 

UPDATE문에 RETURNING * 은 결과값을 반환하나 현재 쿼리에서는

INSERT문을 하지 않기 때문에 'Query returned successfully: 0 rows affected, 87 msec execution time.' 

0 row가 나올 것이고 INSERT문에 RETURNING * 써도 결과값 데이터는 볼 수 없다.

 

만약 INSERT를 하고 바로 결과값을 반환하는 하나의 쿼리를 작성한다면

 

WITH new_rows AS (
  INSERT INTO 테이블명 (기본키컬럼, 컬럼1,컬럼2)
  VALUES ('기본키데이터','컬럼데이터','컬럼데이터2')
  RETURNING *
)
SELECT *
FROM new_rows;

 

이런식으로 작성할 수도 있다.