[DB 변경시 문법변경] Oracle(오라클) or cubrid(큐브리드) --> postgresql(포스트그레)

[DB 변경시 문법변경] Oracle(오라클) or cubrid(큐브리드) --> postgresql(포스트그레)

(Oracle에서 PostgreSQL로 마이그레이션, cubrid에서 PostgreSQL로 마이그레이션)

개발을 하다보면 DB 를 변경해야할때가 있습니다...

그래서 이번에 큐브리드에서 포스트그로 DB 변경하면서 겪었던 달른 문법 및 함수에 대해서 정리해 보았습니다.

필요하신분은 참조하세요! ^^

(아! 그리고 큐브리드랑 오라클이랑 문법은 비슷하니 큐브리드문법을 오라클 문법으로 생각하시고 보시면 됩니다.)

// DATA

DATETIME -> TIMESTAMP

max varchar size -> 10485760

#'integer'# -> ('0'||#'integer'#)::integer

 

sws_hist.tpk -> size 조정 필요 (varchar(64))

 

 

//암호화 SHA256

--암호화 모듈 활성화(9.0 이상)

CREATE EXTENSION [모듈 명]

CREATE EXTENSION pgcrypto;

 

//getCodeNM()

CREATE FUNCTION getcodenm(text) RETURNS text AS $$

SELECT code_nm FROM sws_code WHERE code = $1;

$$ LANGUAGE SQL;

 

//digest()

CREATE OR REPLACE FUNCTION digest( bytea, text)

RETURNS bytea AS

'$libdir/pgcrypto', 'pg_digest'

LANGUAGE c IMMUTABLE STRICT

COST 1;

 

//sha256()

CREATE OR REPLACE FUNCTION sha256(bytea)

RETURNS text AS

$BODY$

SELECT encode(digest($1, 'sha256'), 'hex')

$BODY$

LANGUAGE sql IMMUTABLE STRICT

COST 100;

 

 

//ISNULL, NVL, DECODE

 

(DECODE)

CASE WHEN '대상값' IS NULL THEN '대체1' ELSE CAST('대체2' AS '타입') END

CASE WHEN '대상값' = '비교값' THEN '대체1' ELSE CAST('대체2' AS '타입') END

 

(NVL)

COALESCE('대상값','대체값')

 

 

//CONNECT BY LEVEL

(cubrid)

SELECT TO_DATE(#start_dt#, 'YYYY-MM-DD')+LEVEL-1 cal_dt

FROM db_root

CONNECT BY LEVEL <= (TO_DATE(#end_dt#, 'YYYY-MM-DD')-TO_DATE(#start_dt#, 'YYYY-MM-DD')+1)

 

(postgres)

SELECT generate_series(TO_DATE(#start_dt#, 'YYYY-MM-DD'), TO_DATE(#end_dt#, 'YYYY-MM-DD'), interval '1 day') cal_dt

 

 

// 페이징

(cubrid)

FOR ORDERBY_NUM() BETWEEN (#cpage# * #rows#) - (#rows# - 1) AND #cpage# * #rows#

 

(postgres)

LIMIT #rows#::integer OFFSET (#cpage#::integer-1) * #rows#::integer

 

 

//CONNECT BY PRIOR

(cubrid)

SELECT

LEVEL lv,

menu_nm

FROM

sws_menu

WHERE p_menu_seq!=0

AND del_yn='N' AND public_yn='Y'

START WITH menu_seq=#menu_seq#

CONNECT BY PRIOR p_menu_seq=menu_seq

ORDER BY LEVEL DESC

 

(postgres)

WITH RECURSIVE q AS (

SELECT po.menu_seq, po.menu_nm, po.p_menu_seq

FROM sws_menu as po

WHERE po.menu_seq = #menu_seq#::integer

UNION ALL

SELECT po.menu_seq, po.menu_nm, po.p_menu_seq

FROM sws_menu as po

JOIN q ON q.p_menu_seq = po.menu_seq

WHERE po.p_menu_seq != '0' AND po.del_yn='N' AND po.public_yn='Y'

)

SELECT (ROW_NUMBER() OVER()) AS lv, q.menu_nm

FROM q

ORDER BY lv DESC

 

(postgres 형식)

 

WITH RECURSIVE &결과테이블& AS (

SELECT

FROM &부모테이블&

WHERE &부모 조건&

UNION ALL

SELECT

FROM &자식테이블&, &부모테이블&

WHERE &자식 조건&

)

SELECT

FROM &결과테이블&

-> 결과테이블 = 부모테이블 + 자식테이블s….

 

 

//날짜함수

sysdatetime -> CURRENT_TIMESTAMP

sysdate -> CURRENT_DATE

 

//형변환

cast('대상값' AS '타입')

혹은

'대상값'::'타입'

 

//GROUP_CONCAT

(cubrid)

GROUP_CONCAT( 'expression' SEPARATOR 'delimiter' )

 

(postgres)

9.0 : STRING_AGG( 'expression' , 'delimiter' )

혹은

8.0 : ARRAY_TO_STRING(ARRAY_AGG('expression'), ',')

 

 

 

//OUTER JOIN

(+) 사용 불가 -> OUTER JOIN 으로 대체

 

(cubrid)

SELECT * FROM tbl1 t1, tbl2 t2 WHERE t1.a = t2.a(+)

 

(postgres)

SELECT * FROM tbl1 t1 LEFT OUTER JOIN tbl2 t2 ON t1.a = t2.a

 

 

//serial nextval

 

(cubrid)

SELECT sws_p_proc_serial.nextval

 

(postgres)

SELECT nextval('sws_p_proc_serial')

 

 

 

 

 

//ADD_MONTH

(cubrid)

ADD_MONTHS( TO_DATE(TO_CHAR(CURRENT_DATE,'yyyymm')||'01','yyyymmdd'), -4)

 

(postgres)

TO_DATE(TO_CHAR(CURRENT_DATE,'yyyymm')||'01','yyyymmdd') + ( -4 * '1 month' ::INTERVAL )

혹은

CURRENT_DATE::timestamp + '4 month'

 

//DATEDIFF

(cubrid)

DATEDIFF(&date1&,&date2&)

 

(postgres)

&date1& - &date2&

 

(function)

CREATE OR REPLACE FUNCTION datediff(date, date) RETURNS integer AS

SELECT $1-$2;

$$ LANGUAGE SQL;

 

 

//WEEK

(postgres)

TO_CHAR('date1','IW');

 

(function)

CREATE FUNCTION WEEK(timestamptz) RETURNS integer AS $$

SELECT (TO_CHAR($1,'IW')::integer);

$$ LANGUAGE SQL;

 

 

//DAYOFWEEK

(postgres)

EXTRACT(DOW FROM &date1&)

 

(fucntion)

CREATE FUNCTION DAYOFWEEK(timestamptz) RETURNS integer AS $$

SELECT (EXTRACT(DOW FROM $1)::integer)+1;

$$ LANGUAGE SQL;

 

 

 

//TRUNC

(cubrid)

TRUNC(&date1&, &mod&)

 

(postgres)

DATE_TRUNC(&mod&, &date1&)

 

 

//INSTR

(cubrid)

INSTR(&target&,&word&)

 

(postgres)

POSITION( &word& IN &target& )