PostgreSQL 쿼리(ORACLE과 비교)

sysdate

oracle : select sysdate from dual;
postgresql : select now();

dual table

oracle : select 1,2 from dual;
postgresql : select 1,2;

sequence

oracle : sequence_name.nextval
postgresql : nextval.sequence_name

decode

case문을 쓰거라

NVL

oracle : NVL(hire_date, SYSDATE) - 타입 불일치 시 묵시적 형변환 발생
postgresql : coalesce(hire_date, SYSDATE) - 컬럼타입 불일치 시 오류(상수는 OK)

from절 subquery

oracle : select * from (select * from table_name);
postgresql : select * from (select * from table_name) as alias_name;

outer join

oracle : select a.field1, b.field2 from a, b where a.item_id = b.item_id(+);
postgresql : select a.field1, b.field2 from a left outer join b on a.item_id = b.item_id;

connected by

oracle : connected by
postgresql : with recursive

CLOB

oracle : CLOB
postgresql : TEXT

ROWNUM

oracle : ROWNUM
postgresql : LIMIT