Oracle vs PostgreSQL

오픈소스DB

얼마전 회사에서 운영하선 시스템이 오픈소스DB로 DB를 전환했다. 기존에 사용하던 Oracle에서 PostgreSql로 일부 DB가 변경되었다. 이에 따라 짜여져있던 쿼리들을 기존 Oracle 문법에서 PostgreSql 문법으로 전환해야 하는 부분이 많이 발생하여 문법적차이를 정리해둘 필요성을 느껴 정리를 한다.

개인적으로 DB에 로직이 많이 있는 경우엔, PostgreSql이 알맞은 DB인지에 대해 조금 의문이 들었다. 포스트그레에선 한 트랜잭션이 실행된 후, 해당 트랜잭션이 종료되기 전까지는 락이 걸려 다른 쿼리가 실행되지 않는다. 이로 인해, 운영을 하는 데에는 다소 불편함이 존재하고있다. 물론 이렇게 느끼는 데에는, DB만 도입했을 뿐, DB의 특성을 고려하지 못하고 전환을 했다는 점, 기존 시스템의 문제 등 다양한 이유가 있겠지만.. 요즘 많은 회사들이 PostgreSql을 쓴다는데, 어떻게 설계해서 쓰고 있는 것인지 궁금하다. 여하튼 우리 회사에서 현재 운영하고 있는 시스템에 DB만 갈아끼워서 그런건지 아직 갈길이 멀다.. 예기치 못한 에러도 많고..

다만, 커밋/롤백을 수시로 확인하고 내가 실행시킨 트랜잭션을 계속 점검하는 습관이 생기는 것은 좋은 것 같다.

Oracle과 PostgreSql의 주요 문법 차이

Oracle	Postgre	비고
DECODE	case when	Postgre에선 DECODE가 없다. 그래서 case when then 구문을 사용해야함.
sysdate	Now() || current_timestamp() || clock_timestamp()	현재 날짜 시간을 now() 함수 사용해야한다
시퀀스명.NEXTVAL	NEXTVAL('시퀀스명')	PostgreSQL에선 시퀀스(SEQUENCE) 문법이 다름. || nextval.시퀀스명
NVL	COALESCE	oracle의 경우 타입 불일치시 묵시적 형변환 발생 / postgre의 경우 컬럼 타입 불일치 시 오류 (상수는 ok)
CLOB	TEXT	*clob이란? 사이즈가 큰 데이터를 외부 파일로 저장하기 위한 데이터 타임, 문자열 데이터를 DB외부에 저장하기 위한 타입.
(+)	Outer join	Outer join 시 (+) 사용 불가
Select '컬럼' from dual	Select '컬럼' from	PostgreSQL에서는 dual 사용 불가. Dual을 제외하고 사용.
CAST	::(변환할 데이터 타입)
Select from (select from table_name);	Select from (select from table_name) as alias_name;	From 절 subquery 사용시 alias 사용
Connected by	With recursive	Connected by 절 사용시 with recursive 사용
문자/숫자 비교 묵시적 형변환	문자 / 숫자 비교시 오류	수치 비교시 타입 다르면 오류
number	numeric	데이터 타입 차이
Varchar2	Varchar	데이터 타입 차이
rownum <	limit	Where 절
rownum	Row_number() over() as rownum	Select 절
Instr()	Position()	문자열 index 찾는 함수 instr(문자열, 검색할 문자) -- 해당 문자가 시작되는 위치 반환 instr(문자열, 검색할 문자, 시작지점, n번째 검색단어) -- 문자열의 시작지점 설정, & n번째 나오는 위치 반환