Oracle 쿼리 VS PostgreSQL 쿼리 문법 비교 분석🐘

안녕하세요!

최근 탈(脫) 오라클 바람이 불면서 PostgreSQL(이하 포스트그레스)로 마이그레이션 하시는 분들 많으시죠?

처음엔 "DB가 다 거기서 거기지"라고 생각했다가, 막상 데이터를 옮기고 애플리케이션을 띄워보면 온갖 에러 메시지에 당황하기 십상입니다. 오라클의 '친절한(하지만 독자적인)' 문법에 익숙해진 우리에게 포스트그레스의 '엄격한 표준 준수'는 때론 답답하게 느껴질 수도 있거든요.

 

오늘은 'Oracle vs PostgreSQL 문법 및 기능 차이'를 핵심만 쏙 뽑아 정리해 드립니다.

이 글 하나면 삽질의 시간을 절반으로 줄이실 수 있을 겁니다.

 

---

0. 먼저, 어떤 버전을 써야 할까?

본격적인 문법 비교에 앞서, 엔터프라이즈 환경에서 가장 많이 쓰는 '국룰' 버전부터 짚고 넘어갑시다.

• Oracle: 역시 19c가 가장 안정적이고 많이 쓰입니다. 최근엔 AI 기능이 강화된 23ai로 넘어가는 추세지만, 아직은 19c가 현역 깡패죠.
• PostgreSQL: 매년 새 버전이 나오는데, 현재(2024년 기준) 가장 추천하는 건 PostgreSQL 16 또는 17입니다. 특히 16버전부터는 쿼리 성능이나 복제 기능이 정말 좋아졌고, 오라클 호환성도 많이 개선되었습니다.

그럼 이제부터 Oracle과 Postgresql 간의 문법 차이 하나씩 공유 드리겠습니다.

 

 

---

1. Join 문법: (+) 기호 사용 불가

오라클 쿼리의 상징과도 같았던 (+) 아우터 조인 문법, 포스트그레스에서는 에러 납니다. 이제 표준인 ANSI Join을 받아들이셔야 합니다.

• 설명: 오라클은 WHERE 절에 조인 조건을 섞어 쓰지만, 포스트그레스는 FROM 절에서 명확하게 JOIN ON으로 풀어내야 합니다. 가독성도 훨씬 좋습니다.
• Oracle (Legacy) :
  

  SELECT e.ename, d.dname
  FROM emp e, dept d
  WHERE e.deptno = d.deptno(+);  -- 오라클 특유의 Right Outer Join 표기
  

   
• PostgreSQL (ANSI) :
  

  SELECT e.ename, d.dname
  FROM emp e
  LEFT OUTER JOIN dept d ON e.deptno = d.deptno; -- 깔끔하죠?
  

 

2. DUAL 테이블: 굳이 없어도 됩니다

오라클에선 SELECT 뒤에 FROM이 없으면 허전해서 꼭 FROM DUAL을 썼죠? 포스트그레스는 쿨합니다. 그냥 생략하세요.

• 설명: 포스트그레스는 상수나 함수 결과만 조회할 때 FROM 절이 필요 없습니다. (물론 호환성을 위해 DUAL 뷰를 만들어두기도 하지만, 정석은 생략입니다.)
• Oracle : 
  

  SELECT sysdate FROM dual;
  

• PostgreSQL :
  

  SELECT now();
  

3. Sysdate vs Now(): 시간이 멈춘다? 

오라클의 SYSDATE은 Postgresql에서는 사용하지 못합니다.

• 설명: 오라클 SYSDATE는 쿼리가 실행되는 순간의 시간을 가져옵니다. 반면 포스트그레스의 now()는 트랜잭션이 시작된 시간을 가져옵니다. 긴 배치 돌릴 때 now() 쓰면 로그 시간이 다 똑같이 찍히는 마법을 보게 됩니다. 실시간 시간이 필요하면 statement_timestamp () (clock_timestamp() )혹은 를 쓰세요.
• Oracle: SYSDATE
• PostgreSQL: now() (트랜잭션 시간), statement_timestamp() (실제 시간. 동일 쿼리 일 경우 동일한 시간), clock_timestamp() (실제 시간. 동일 쿼리 문장 내에서도 문장이 실행된 시간)

4. 날짜 연산: Add_months는 가라, Interval이 왔다

오라클의 ADD_MONTHS는 정말 편한 함수였죠. 포스트그레스는 좀 더 직관적인 영어 문법(INTERVAL)을 사용합니다.

• 설명: 포스트그레스는 + 연산자에 문자열로 기간을 더하는 방식을 씁니다.
• Oracle :
  

  SELECT ADD_MONTHS(sysdate, 1) FROM dual;
  

  
  
• PostgreSQL :
  

  SELECT now() + INTERVAL '1 month'; -- '1 year', '2 days' 등 다양하게 가능
  

  
  

5. DECODE vs CASE WHEN: 표준을 따르라

오라클의 효자 함수 DECODE, 아쉽게도 포스트그레스 기본 함수엔 없습니다.

• 설명: 표준 SQL인 CASE WHEN 구문으로 바꿔야 합니다. 처음엔 길어서 귀찮지만, 복잡한 로직 구현엔 이게 훨씬 강력합니다.
• Oracle: 

  SELECT DECODE(deptno, 10, 'A', 'B') FROM emp;
  

• PostgreSQL:

  SELECT CASE deptno WHEN 10 THEN 'A' ELSE 'B' END FROM emp;
  

 

6. Sequence: NEXTVAL의 위치가 다르다

 

시퀀스 값을 가져오는 방법도 미묘하게 다릅니다.

• 설명: 오라클은 객체의 메서드처럼 .nextval을 쓰지만, 포스트그레스는 함수 안에 시퀀스 이름을 넣습니다.
• Oracle : seq_no.NEXTVAL
• PostgreSQL : nextval('seq_no')

 

7. NULL 처리: NVL 대신 COALESCE

NULL을 다른 값으로 치환할 때 쓰는 NVL 함수, 포스트그레스에선 COALESCE(콜레스라고 읽습니다)를 씁니다.

• 설명: COALESCE는 인자를 여러 개 받을 수 있어서 NVL보다 더 유연합니다. (널이 아닌 첫 번째 값을 반환)
• Oracle: NVL(col, 0)
• PostgreSQL: COALESCE(col, 0)

 

8. UPDATE 문: 타겟 테이블에 별칭(Alias) 금지! 

이거 습관적으로 쓰다가 에러 정말 많이 냅니다.

• 설명: 오라클은 업데이트 대상 테이블에 별칭을 주고 SET 절에서 그 별칭을 썼죠? 포스트그레스는 업데이트 바로 뒤에 오는 테이블명엔 별칭을 못 붙입니다.
• Oracle: 

  UPDATE emp e SET e.sal = 1000 WHERE e.id = 1;
  

• PostgreSQL:

  UPDATE emp SET sal = 1000 WHERE id = 1; -- emp 뒤에 'e' 붙이면 에러남!
  

 

9. 계층형 쿼리: START WITH... CONNECT BY 사용 불가

 

오라클의 강력한 무기였던 계층형 쿼리, 포스트그레스에선 Recursive CTE (재귀 쿼리)로 풀어야 합니다.

• 설명: WITH RECURSIVE 구문을 사용해서 부모-자식 관계를 정의해야 합니다. 코드는 좀 길어지지만 논리는 명확합니다.
• Oracle: START WITH... CONNECT BY PRIOR...
  

  SELECT 
      emp_id, 
      name, 
      manager_id, 
      LEVEL  -- 계층의 깊이 (오라클 내장 의사 컬럼)
  FROM employees
  START WITH manager_id IS NULL          -- 루트 노드(최상위 관리자) 설정
  CONNECT BY PRIOR emp_id = manager_id;  -- 부모(PRIOR)의 ID가 자식의 manager_id와 같음

• PostgreSQL: WITH RECURSIVE CTE AS (...) SELECT * FROM CTE
  

  WITH RECURSIVE employee_hierarchy AS (
      -- [1] Anchor Member (비재귀 부분): START WITH에 해당
      -- 최상위 관리자(루트 노드)를 먼저 선택합니다.
      SELECT 
          emp_id, 
          name, 
          manager_id, 
          1 AS level  -- LEVEL 초기값 설정
      FROM employees
      WHERE manager_id IS NULL
      UNION ALL
      -- [2] Recursive Member (재귀 부분): CONNECT BY에 해당
      -- 위에서 선택된 부모(h)와 자식(e)을 조인하여 하위 레벨을 찾습니다.
      SELECT 
          e.emp_id, 
          e.name, 
          e.manager_id, 
          h.level + 1 AS level  -- 레벨을 1씩 증가
      FROM employees e
      INNER JOIN employee_hierarchy h ON h.emp_id = e.manager_id
  )
  -- [3] 최종 결과 조회
  SELECT * FROM employee_hierarchy;

  
  
  

 

10. 페이징 처리: ROWNUM은 잊어라, LIMIT가 있다

오라클에서 ROWNUM으로 페이징 하려면 서브쿼리 3단으로 감싸고 난리였죠? 포스트그레스는 세상 심플합니다.

• 설명: LIMIT과 OFFSET으로 끝납니다.
• Oracle: WHERE ROWNUM <= 10 (물론 정렬하려면 복잡해짐)
• PostgreSQL: ORDER BY id LIMIT 10 OFFSET 0

 

11. 타입(Type)의 엄격함: 자동 형변환은 없다 (:: 캐스팅)

오라클은 숫자 컬럼에 문자열 '123'을 넣어서 비교해도 찰떡같이 알아듣습니다. 하지만 포스트그레스는 깐깐한 교수님 같습니다. "타입이 틀렸잖아!" 하고 에러를 뱉습니다.

• 설명: 명시적으로 형변환을 해줘야 합니다. :: 연산자를 애용하세요. 특히 빈 문자열 ''을 숫자로 바꾸려 하면 오라클은 NULL로 봐주지만, 포스트그레스는 에러 냅니다.
• Oracle: SELECT * FROM table WHERE num_col = '123' (자동 변환됨)
• PostgreSQL: SELECT * FROM table WHERE num_col = '123'::integer (혹은 CAST(col as AS BIGINT), CAST(col as TEXT))

 

12. Like 연산자: 숫자한테 그러지 마세요

오라클에선 숫자 타입 컬럼에도 LIKE '123%' 같은 검색이 됐지만, 포스트그레스는 오직 문자열에만 LIKE를 쓸 수 있습니다. 숫자를 문자로 캐스팅(::text)한 뒤에 써야 합니다.

 

 

13. 문자열 합치기: LISTAGG vs STRING_AGG

그룹바이 할 때 문자열 합치는 함수의 문법 순서가 살짝 다릅니다.

• Oracle: LISTAGG(col, ',') WITHIN GROUP (ORDER BY col)
• PostgreSQL: string_agg(col, ',' ORDER BY col)

 

14. TO_DATE 포맷의 차이

오라클의 TO_DATE는 시분초까지 다루지만, 포스트그레스의 to_date는 정말 '날짜(년월일)'만 가져옵니다. 시간까지 필요하면 반드시 to_timestamp를 써야 합니다. 이거 실수해서 시간 데이터 다 날리는 경우 많이 봤습니다. 주의하세요!

 

15. 가장 중요한 차이: NULL과 빈 문자열('')

이것 때문에 마이그레이션 프로젝트 막판에 데이터 안 맞는다고 야근합니다. 별 다섯 개 ⭐⭐⭐⭐⭐

• Oracle: 오라클에서 빈 문자열 ''은 NULL과 같습니다. (IS NULL로 검색됨)
• PostgreSQL: 엄연히 다릅니다! ''은 그냥 길이가 0인 문자열이고, NULL은 값이 없는 상태입니다.
• 해결: 오라클처럼 비교하고 싶다면 col IS NULL OR col = '' 처럼 둘 다 체크하거나, 입력 단계에서 NULLIF(col, '')를 써서 빈 문자열을 강제로 NULL로 만들어야 합니다. 또한 COALESCE  사용하여 치환 시에도 NULLIF 를 통해서 ''값을 NULL로 치환 후 COALESCE 처리하는게 좋습니다(예 : COALESCE(NULLIF(col, ''), 0))

 

16. Substr 인덱스: 0이냐 1이냐

오라클은 SUBSTR(str, 0, 1)이나 SUBSTR(str, 1, 1)이나 똑같이 첫 글자를 가져옵니다(관대함). 포스트그레스는 인덱스가 1부터 시작합니다. 0을 넣으면 에러가 잘못된 값을 가져올 수 있습니다. 무조건 1부터 시작하게 코드를 고치세요.

 

17. 문자열 연결 (||) 과 NULL

오라클은 `'A' || NULL하면'A'가 나옵니다. (NULL을 빈 문자 취급). 포스트그레스는 NULL과 더해지는 순간 결과가 NULL`이 되어버립니다.

그렇기 때문에 CONCAT('A', NULL) 함수를 쓰면 오라클처럼 NULL을 무시하고 합쳐줍니다. || 연산자 쓸 거면 COALESCE로 NULL 방어 처리를 해야 합니다.

 

 

오라클에서 포스트그레스로 넘어가는 건 단순한 DB 교체가 아니라, '데이터를 다루는 철학'이 바뀌는 과정입니다.

오라클이 "알아서 해줄게"라면, 포스트그레스는 "네가 명확하게 말해"라는 식이죠.

처음엔 깐깐해 보여도, 적응하고 나면 포스트그레스의 명확함과 확장성에 반하게 되실 겁니다. (물론 라이선스 비용 절감 효과를 보며 좋아하는 사장님의 미소는 덤이죠 )

이 글이 여러분의 퇴근 시간을 1시간이라도 앞당겨 주길 바라며, 오늘도 데이터와 사투를 벌이는 모든 엔지니어분들 파이팅입니다

 

---

참고한 자료 & 도움 되는 사이트

• (https://www.postgresql.org/support/versioning/)
• Oracle to Postgres 변환 위키 (공식)
• (https://aws.amazon.com/blogs/database/migrate-date-functions-from-oracle-to-amazon-rds-for-postgresql/)
• (https://www.enterprisedb.com/blog/how-null-and-empty-strings-are-treated-postgresql-vs-oracle)