[PostgreSql] 오라클에서 postgresql 로 이관시 타입변환 정리

오라클에서 MERGE INTO 문은 postgresql에서는 UPSERT 문과 비슷하지만 다르다. 오라클에서는 한번에 조회해서 update나 insert처리가 되지만 postgresql에서는 한건씩 비교해서 같으면 업데이트 다르면 인서트 처리를 한다. 건수가 많지 않을경우에는 cursor돌려서 처리하면 되지만 몇백만건 이상 될때는 속도 차이가 많이 난다.

 

FOR 루프로 100만건 정도 된다고 하면 한건싹 루프 돌면서 처리가 되어서 시간이 오래 걸림 

FOR ROW_DATA IN ( 
         SELECT KEY,TEST1,TEST2 
           FROM 조회_테이블명
         )
    LOOP
        
	WITH UPSERT AS
		(   
         UPDATE 테이블명
            SET TEST1 = ROW_DATA.TEST1
              , TEST2 = ROW_DATA.TEST2
              , REG_DATE = NOW()
         WHERE KEY = ROW_DATA.KEY 
          RETURNING *
         )
    INSERT INTO 테이블명 ( KEY,TEST1,TEST2,REG_DATE )
     SELECT ROW_DATA.KEY, ROW_DATA.TEST1, ROW_DATA.TEST2, NOW() 
     WHERE NOT EXISTS(SELECT * FROM UPSERT)
    ;
   END LOOP;

아래와 같은 방법으로 대량일때 처리하면 처리 속도가 개선됨

/* 업데이트와 인서트 부분을 분리하여 처리한다. */
/* 업데이트 처리 */
UPDATE 테이블명 A
  SET TEST1 = ROW_DATA.TEST1
     , TEST2 = ROW_DATA.TEST2
     , REG_DATE = NOW()
FROM (SELECT KEY,TEST1,TEST2 
        FROM 조회_테이블명
    )B
WHERE A.KEY = B.KEY;

/* 신규데이터 처리 */
INSERT 테이블명 ( KEY,TEST1,TEST2,REG_DATE )
SELECT A.KEY,A.TEST1,A.TEST2,NOW() 
  FROM (
         SELECT KEY,TEST1,TEST2 
           FROM 조회_테이블명
     ) A
   LEFTER OUTER JOIN 테이블명 B
   ON A.KEY = B.KEY
   WHERE B.KEY IS NULL;

- 오라클에서 날짜 타입->POSTGRESQL에서 변환

몇분전 처리

/* 오라클 기준 4분전 */
  SELECT SYSDATE AS SYSDT
         ,TO_DATE(TO_CHAR(SYSDATE-4/24/60, 'YYYYMMDDHH24MI')||'59','YYYYMMDDHH24MISS') AS SYSDT_2    /*현재기준-2분전*/
   FROM DUAL;

/* postgresql 기준 4분전 */
  select  NOW(),  /* 현재일자+시간 */
          NOW() - (4 || ' MINUTES')::interval as beforeMinute /* (4분전) */

/* 몇분전을 가변으로 값이 변할때 변수로 받아서 처리*/
  
  DECLARE
    V_MINUTE_TERM NUMERIC;
    
   BEGIN
     V_MINUTE_TERM := 2;
   
  
  /* postgresql 기준 가변 4분전 */
  select  date '2022-06-28' - (1 || ' month')::interval as beforMonth, /* 한달전 */
          NOW(),  /* 현재일자+시간 */
          NOW() - (V_MINUTE_TERM * 2 || ' MINUTES')::interval as beforeMinute /* 변수 * 2배수 한 분 정보(4분전) */
     FROM 
        (
            select 2 AS V_MINUTE_TERM
        ) A
  END;

4분전 표시

 

-  /* 날짜와 날짜사이 일수 구하기 */
 

   SELECT date_part('DAY',to_timestamp('20220628','YYYYMMDD') - to_timestamp('20220626','YYYYMMDD')) as day

/* 날짜와 날짜사이 분 구하기 */

  /* 오라클 */
    SELECT TRUNC(( ( to_date('20220628133522') - (to_date('20220627133522')) ) * 24 * 60), 2) AS DATE_TERM FROM DUAL ;
   
  /* POSTGRESQL */              
  SELECT   TRUNC( ( (
              (date_part('DAY',to_timestamp('2022062813522','YYYYMMDDhh24miss')  - to_timestamp('20220627133522','YYYYMMDDhh24miss') )) * 24 * 60 * 60
           +  (date_part('HOUR',to_timestamp('20220628133522','YYYYMMDDhh24miss')  - to_timestamp('20220627133522','YYYYMMDDhh24miss') )) * 60 * 60
           + (date_part('MIN',to_timestamp('20220628133522','YYYYMMDDhh24miss')  - to_timestamp('20220627133522','YYYYMMDDhh24miss') )) * 60 
           + (date_part('SECOND',to_timestamp('20220628133522','YYYYMMDDhh24miss')  - to_timestamp('20220627133522','YYYYMMDDhh24miss') )) ) / 60) ::numeric ,2) as DATE_TERM ;

문법

해당 함수는 아래와 같이 사용할 수 있습니다.

DATE_PART(field, source)

field란 year, month, day 와 같은 날짜/시간 형태의 요소를 말하고

source란 실제 시간값인 timestamp 값을 의미합니다. '2020-01-01 10:00:00' 와 같은 값입니다.

 

간단히 문법은 살펴보았으니 이제 어떤 인자들이 필요한지 확인해보도록 하겠습니다.

필요인자 정리

field는 위에서 간단히 설명했듯이 날짜/시간 값에서 추출할 필드를 지정하는 인자입니다.

이 부분은 extract 함수의 인자와 같습니다.

여러가지 인자가 있지만 실무에 자주 사용될 법한 인자들을 정리해보았습니다. 

Field 값	의미
CENTURY	세기(21세기, 20세기)
DAY	1~31에 해당하는 해당 월의 일
DOW	일요일(0) ~ 토요일(6)까지 반환하는 값
DOY	1~366 까지 해당하는 연중일수
EPOCH	1970년 1월 1일 00:00:00 UTC 부터 현재까지의 초(unixtime이란 -> sas-study.tistory.com/94)
HOUR	0 ~ 23 에 해당하는 시간정보
MILLISECONDS	1/1000에 해당하는 밀리초
MINUTE	0 ~ 59에 해당하는 분 정보
MONTH	1 ~ 12에 해당하는 월 정보
QUARTER	1(1~3월), 2(4~6월), 3(7~9월), 4(10~12월) 분기로 나뉘어지는 정보
SECOND	0 ~ 59에 해당하는 초 정보
WEEK	주 정보 (1월 1일 : 1, 12월 31일: 52~53)
YEAR	연도 정보.

반환 타입

double precision (0.0)

사용 예제

select date_part('CENTURY'     , '2021-02-03 15:23:22.23242'::timestamp); -- 21
select date_part('DAY'         , '2021-02-03 15:23:22.23242'::timestamp); -- 3
select date_part('DOW'         , '2021-02-03 15:23:22.23242'::timestamp); -- 3
select date_part('DOY'         , '2021-02-03 15:23:22.23242'::timestamp); -- 34
select date_part('EPOCH'       , '2021-02-03 15:23:22.23242'::timestamp); -- 1612365802.23242
select date_part('HOUR'        , '2021-02-03 15:23:22.23242'::timestamp); -- 15
select date_part('MILLISECONDS', '2021-02-03 15:23:22.23242'::timestamp); -- 22232.42
select date_part('MINUTE'      , '2021-02-03 15:23:22.23242'::timestamp); -- 23
select date_part('MONTH'       , '2021-02-03 15:23:22.23242'::timestamp); -- 2
select date_part('QUARTER'     , '2021-02-03 15:23:22.23242'::timestamp); -- 1
select date_part('SECOND'      , '2021-02-03 15:23:22.23242'::timestamp); -- 22.23242
select date_part('WEEK'        , '2021-02-03 15:23:22.23242'::timestamp); -- 5
select date_part('YEAR'        , '2021-02-03 15:23:22.23242'::timestamp); -- 2021

오라클에서 connect by level을 통해서 생성된 날짜를 postgresql에서 똑같이 생성하기 위해서는 아래와 같이 하면 된다.

 

/* 오라클에서 connect by 를 이용해서 생성된 날짜는 */
        SELECT TO_CHAR(SYSDATE + LEVEL-1, 'YYYYMMDD') AS PDATE 
          FROM DUAL 
         CONNECT BY LEVEL <= 7;
        
        /* posrgresql 에서는 generate_series함수를 이용해서 생성하면 결과가 똑같다 */
               
	      SELECT  to_char(a.date + b.idx -1,'YYYYMMDD') as PDATE 
			FROM ( 
			        SELECT CURRENT_DATE AS date
			    ) a 
			CROSS JOIN ( 
			    SELECT generate_series(1, 7) as idx 
             ) b