Oracle To PostgreSQL

 

기본

• Oracle 에서 n 은 바이트 수이고 PostgreSQL 에서는 문자 수 입니다.
• PostgreSQL text 유형은 1GB 까지
• number 유형을 numeric 타입으로 변환 할 수 있지만, smallint , int 및 bigint 사용시 성능이 향상 됨
• Oracle 에서 to_date() 함수는 날짜와 시간을 모두 반환 하지만, PostgreSQL 에서 날짜만 반환 됨 (to_timestamp() 함수를 사용)

--oracle exam SELECT TO_DATE ('20180314121212','yyyymmddhh24miss') FROM dual;  --postgres exam SELECT TO_TIMESTAMP ('20180314121212','yyyymmddhh24miss');

• PostgreSQL 에서는 DUAL 테이블은 필요 없습니다
• PostgreSQL 에서는 DELETE 문에 FROM 절을 명시할 것
• nvl 대신 coalesce
• decode 대신 case
• PostgreSQL 조건절에 매핑되는 컬럼의 타입의 유형에 주의 (특히 double precisioin 속성에 주의)
• rownum 대신 limit (복잡한 쿼리에 limit 사용시 order by 절이 없다면 결과를 보기까지 수 십분이 걸릴 수 있음!!!)

--postgres exam select T1.* from (     select 1 as id, '00' as cd     union      select 2 as id, '00' as cd     union      select 3 as id, '00' as cd ) T1 order by id limit 1;

• Oracle 에서 암묵적인 형 변환이 필요 없지만, PostgreSQL 에서는 필수로 형 변환이 필요 (조건 절에서도 컬럼 속성이 다르다면 문제 발생)

--oracle exam select T1.* from (     select 1 as id, 'name1' as name from dual ) T1 , (     select '1' as id, 'name1' as name from dual ) T2 where T1.id = T2.id  --postgres exam select T1.* from (     select 1 as id, 'name1' as name ) T1 inner join (     select '1'::int as id, 'name1' as name      --형변환 명시 ) T2 on T1.id = T2.id

 

함수

• SUBSTR() 함수의 동작에 유의

--oracle exam select substr('ABC', -1) from dual;  --postgres exam select substr('ABC', -1);

• WITH CONNECT BY

--oracle exam SELECT     restaurant_name,      city_name  FROM     restaurants rs  START WITH rs.city_name = 'SEOUL' CONNECT BY PRIOR rs.restaurant_name = rs.city_name;  --postgres exam WITH RECURSIVE tmp AS (SELECT restaurant_name, city_name                                  FROM restaurants                                 WHERE city_name = 'SEOUL'                                 UNION                                SELECT m.restaurant_name, m.city_name                                  FROM restaurants m                                  JOIN tmp ON tmp.restaurant_name = m.city_name)                   SELECT restaurant_name, city_name FROM tmp;

• LISTAGG WITH GROUP(ORDER BY ...)

--oracle listagg 문  SELECT LISTAGG(aa, '|') WITHIN GROUP (ORDER BY aa)  FROM (     SELECT 'a' AS aa, 1 AS num FROM dual   UNION    SELECT 'c' AS aa, 3 AS num FROM dual   UNION    SELECT 'b' AS aa, 2 AS num  FROM dual   UNION    SELECT 'a' AS aa, 4 AS num  FROM dual ) t;  --postgresql exam 1 SELECT STRING_AGG(t1.a, '|' ORDER BY t1.num) AS a FROM (         SELECT 'a' AS a, 1 AS num         UNION         SELECT 'c' AS a, 3 AS num         UNION         SELECT 'b' AS a, 2 AS num         union         SELECT 'a' AS a, 4 AS num ) t1 ;  --postgresql exam 2 SELECT ARRAY_TO_STRING(ARRAY_AGG(t2.a ORDER BY t2.a), '|') FROM (         SELECT 'a' AS a, 1 AS num         UNION         SELECT 'c' AS a, 3 AS num         UNION         SELECT 'b' AS a, 2 AS num         union         SELECT 'a' AS a, 4 AS num ) t2;

• MAX (or MIN) KEEP

--oracle exam  SELECT MAX(SEND_BACK_YN) KEEP(DENSE_RANK FIRST ORDER BY SEQ DESC)   FROM SRCH_EXEC_SEND_BACK_INFO F                        WHERE F.SRCH_SEQ = '5-2013-00001902';   --postgresql exam SELECT * FROM (         SELECT SEND_BACK_YN             , ROW_NUMBER() OVER(PARTITION BY SRCH_SEQ ORDER BY SEQ DESC) Corr      FROM SRCH_EXEC_SEND_BACK_INFO      WHERE SRCH_SEQ = '5-2013-00001723' ) AS RS1  WHERE Corr = 1; 

 

그 외 지원

• MERGE

--oracle exam MERGE INTO my_table a     USING (         SELECT v_c1 key                    ,v_c2 AS pkey                    ,v_c3 AS wcount                    ,v_c4 AS dcount         FROM DUAL     ) b     ON (a.key = b.key AND a.pkey = b.pkey) WHEN MATCHED THEN    UPDATE        SET wcount = b.wcount               ,dcount = b.dcount WHEN NOT MATCHED THEN    INSERT (key                ,pkey                ,wcount                dcount)     VALUES (b.key, b.pkey, b.wcount, b.dcount) ;  --postgresql exam with cte_dual as (     SELECT v_c1 as key         ,v_c2 as pkey         ,v_c3 as wcount         ,v_c4 as dcount ), cte_update as (     update my_table as a         SET wcount = b.wcount               , dcount = b.dcount     from cte_dual as b     where b.key = a.key         and b.pkey = a.pkey     returning * ) insert into my_table (key, pkey, wcount, dcount) select d.key, d.pkey, d.wcount, d.dcount from cte_dual as d where not exists (     select * from cte_update as u WHERE u.key = d.key and u.pkey = d.pkey ) ;

• REGEXP_LIKE

--oracle exam SELECT REGEXP_LIKE('ABCDE123', '^[0-9]') from dual;  --postgresql exam SELECT 'ABCDE123' ~ '[0-9]';