[postgreSql] with문을 사용하여 update와delete 대상이 없을 때 에만 INSERT 처리 하는 방법

backend 에서 루프를 돌리면 속도 이슈가 있어서 원쿼리로 해결하는 방법이다. 아래 쿼리는 select 결과 데이터를 기준으로 TB_VERSION_DTL테이블에 데이터가 존재하면 UPDATE를 하고, 특정 조건에서는 DELETE처리를 한다.

그리도 UPDATE, DELETE 모두 없을 경우에만 INSERT처리하는 쿼리이다.

WITH SEL_WITH AS (
	SELECT
		A.COMPANY_NO
		,B.SERVICE_NO
		,B.DEVICE_ID
		,#{nextDeviceInoutVersion} AS DEVICE_INOUT_VERSION
		,#{actionType} AS ACTION_TYPE
		,IIF( COALESCE(#{schctionType}, 'A') = 'D', 'N', IIF(C.SETTING_DATE IS NULL, 'N', 'Y') ) AS SCHEDULE_USE_YN
		,#{regId} AS REG_ID
		,NOW() + (ROW_NUMBER() OVER()) * interval '1 millisecond' AS REG_DT
	FROM TB_USER A
	INNER JOIN (
		SELECT
			T1.COMPANY_NO
			,T1.SERVICE_NO
			,T1.DOOR_DEVICE_ID AS DEVICE_ID
			,T3.USER_SEQ
		FROM TB_GROUP_DOOR_MATCH T1
		INNER JOIN TB_CUSTOMER_GROUP_INFO T2
			ON T1.GROUP_SEQ = T2.SEQ
		INNER JOIN TB_GROUP_USER_MATCH T3
			ON T1.COMPANY_NO = T3.COMPANY_NO
			AND T1.GROUP_SEQ = T3.GROUP_SEQ
		WHERE T1.COMPANY_NO = #{contractNo}
		AND T1.SERVICE_NO = #{serviceNo}
		AND T1.DOOR_DEVICE_ID = #{deviceId}
		AND EXISTS (SELECT 1 FROM TB_USER WHERE SEQ = T3.USER_SEQ AND COALESCE(USE_YN, 'Y') = 'Y' AND COALESCE(DEL_YN, 'N') = 'N')
		UNION
		SELECT
			T1.COMPANY_NO
			,T1.SERVICE_NO
			,T1.DEVICE_ID
			,T1.USER_SEQ
		FROM TB_CARD_DOOR_MATCH T1
		WHERE T1.COMPANY_NO = #{contractNo}
		AND T1.SERVICE_NO = #{serviceNo}
		AND T1.DEVICE_ID = #{deviceId}
		AND EXISTS (SELECT 1 FROM TB_USER WHERE SEQ = T1.USER_SEQ AND COALESCE(USE_YN, 'Y') = 'Y' AND COALESCE(DEL_YN, 'N') = 'N')
	) B
	ON A.COMPANY_NO = B.COMPANY_NO
		AND A.SEQ = B.USER_SEQ
	LEFT JOIN TB_DOOR_SCHEDULE C
	ON A.COMPANY_NO = C.COMPANY_NO
		AND A.SEQ = C.USER_SEQ
		AND B.DEVICE_ID = C.DEVICE_ID
	WHERE A.SEQ = #{cardUserSeq}
		AND COALESCE(A.USE_YN, 'Y') = 'Y'
		AND COALESCE(A.DEL_YN, 'N') = 'N'
), 
--UPDATE 대상
UPDATED AS (
	UPDATE TB_VERSION_DTL T1 SET
		ACTION_TYPE = SEL_WITH.ACTION_TYPE
		,UPT_ID = SEL_WITH.REG_ID
		,UPT_DT = SEL_WITH.REG_DT
	FROM SEL_WITH
	WHERE T1.COMPANY_NO = SEL_WITH.COMPANY_NO
			AND T1.SERVICE_NO = SEL_WITH.SERVICE_NO
			AND T1.DEVICE_ID = SEL_WITH.DEVICE_ID
			AND T1.DEVICE_INOUT_VERSION = SEL_WITH.DEVICE_INOUT_VERSION
			AND T1.REGIST_ID = SEL_WITH.REGIST_ID
			AND SEL_WITH.ACTION_TYPE = 'A'
	RETURNING *
), 
--삭제 대상
DELETED AS (
	DELETE FROM TB_VERSION_DTL T2
	USING SEL_WITH
	WHERE T1.COMPANY_NO = SEL_WITH.COMPANY_NO
			AND T1.SERVICE_NO = SEL_WITH.SERVICE_NO
			AND T1.DEVICE_ID = SEL_WITH.DEVICE_ID
			AND T1.DEVICE_INOUT_VERSION = SEL_WITH.DEVICE_INOUT_VERSION
			AND T1.REGIST_ID = SEL_WITH.REGIST_ID
			AND SEL_WITH.ACTION_TYPE = 'D'
	RETURNING *
)
-- UPDATE + DELETE 결과를 합침
UPSERT AS (
	SELECT * FROM UPDATED
	UNION ALL
	SELECT * FROM DELETED
)
-- UPDATE/DELETE 결과 없는 경우만 INSERT
INSERT INTO TB_VERSION_DTL (
	COMPANY_NO
	,SERVICE_NO
	,DEVICE_ID
	,ACTION_TYPE
	,REG_ID
	,REG_DT
)
SELECT
	COMPANY_NO
	,SERVICE_NO
	,DEVICE_ID
	,ACTION_TYPE
	,REG_ID
	,REG_DT
FROM SEL_WITH
WHERE NOT EXISTS(SELECT 1 FROM UPSERT)

아래처럼 하나에 묶어서 하려고 시도하였으나, 오류가 발생하니, 아래와 같은 방식으로 사용하면 안된다.

Sql Error [42601] ERROR : syntax error at or near “UNION”

이건 PostgreSQL이 CTE 내부에서 UPDATE ... RETURNING 과 DELETE ... RETURNING을 UNION으로 직접 묶는 걸 허용하지 않기 때문이다.

UPDATE ... RETURNING ...
UNION ALL
DELETE ... RETURNING ...

PostgreSQL은 WITH CTE 내에서 각 쿼리 블록은 하나의 문장이어야 한다.

따라서 UPDATE와 DELETE를 별도 CTE로 분리하고, 그 둘을 UNION으로 묶는 건 그 다음 CTE에서 해야 한다.