sqlporting.md

SQL Porting

Oracle 데이터베이스에서 PostgreSQL로 전환하려면 Oracle과 PostgreSQL 간의 함수 차이를 이해하고 적절한 포팅 방법을 익혀야 합니다.

이 과정에는 데이터 타입, SQL 문법, 저장 프로시저 등 여러 요소들이 포함되며, 이들 간의 차이점을 이해하는 것이
중요합니다.
또한, 효율적인 전환을 위해 자동화 도구의 사용도 고려해야 합니다. 이러한 지식을 습득함으로써, Oracle에서 PostgreSQL로의 전환 과정을 원활하게 진행할 수 있습니다

     

1. 날짜와 시간 관련 함수

 

• SYSDATE (Oracle) -> NOW() (PostgreSQL)
  copy

  SELECT NOW();

 

• SYSTIMESTAMP (Oracle) -> CURRENT_TIMESTAMP (PostgreSQL)
  copy

  SELECT CURRENT_TIMESTAMP;

 

• ADD_MONTHS(date, n) (Oracle) -> date + INTERVAL 'n months' (PostgreSQL)
  copy

  SELECT NOW() + INTERVAL '1 MONTH';

 

• LAST_DAY(date) (Oracle) -> DATE_TRUNC(date) + INTERVAL '1 month' - INTERVAL '1 day'
  copy

  SELECT (DATE_TRUNC('MONTH',NOW()) + INTERVAL '1 MONTH' - INTERVAL '1 DAY')::DATE;

   

2. 문자열 함수

 

• SUBSTR(string, start, length) (Oracle) -> SUBSTRING(string FROM start FOR length) (PostgreSQL)
  copy

  SELECT SUBSTRING('ABCDEFG' FROM 2 FOR 3);

 

• INSTR(string, substring) (Oracle) -> POSITION(substring IN string) (PostgreSQL)
  copy

  SELECT POSITION('B' IN 'ABCDEFG');

   

3. 기타 함수

 

• DECODE(expression, search, result, ...) (Oracle) -> CASE 문 사용 (PostgreSQL)
  copy

  SELECT CASE '10' WHEN '10' THEN 'T' ELSE 'F' END;

 

• NVL(expr1, expr2) (Oracle) -> COALESCE(expr1, expr2) (PostgreSQL)
  copy

  SELECT COALESCE(NULL, 'F');  --copy 

 

• NVL2(expr1, expr2, expr3) (Oracle) -> CASE WHEN expr1 IS NOT NULL THEN expr2 ELSE expr3 END (PostgreSQL)
  copy

  SELECT CASE WHEN 'ABCDEF' IS NOT NULL THEN 'F' ELSE 'T' END;

   

4. 구문

 

• ROWNUM (Oracle) -> OFFSET FETCH (PostgreSQL)
  copy

 SELECT *                                                 
  FROM employees
 ORDER BY last_name, first_name 
 OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY ;

 

• MINUS (Oracle) -> EXCEPT (PostgreSQL)
  copy

 SELECT department_id FROM departments
 EXCEPT
 SELECT department_id FROM employees;

 

• (+) (Oracle) -> OUTER JOIN (PostgreSQL)
  copy

 SELECT * FROM employees e
   LEFT OUTER JOIN departments d on (e.department_id = d.department_id);

 

• CONNECT BY START WITH (Oracle) -> WITH RECURSIVE CTE (PostgreSQL)
  copy

SELECT E.*,LEVEL
FROM EMPLOYEES E
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;

copy

 WITH RECURSIVE A AS ( 
 SELECT E.*, 1 LEVEL
 FROM EMPLOYEES E
 WHERE MANAGER_ID IS NULL
 UNION ALL
 SELECT D.*, A.LEVEL + 1
 FROM EMPLOYEES D
 JOIN A ON A.EMPLOYEE_ID = D.MANAGER_ID )
 SELECT * FROM A;