= Oracle 转 PG 汇总 =

== 语法 ==
* rownum 转为 limit 
{{{
oracle:
select title from sns_pclady.msg_message where rownum<=100;

pg:
select title from sns_pclady.msg_message limit 100;
}}}
* 别名要显式指定
{{{
oracle:
select count(*) from ( select * from 
(select * from sns_pclady.msg_message where receiver= 6499175  and  delbyreceiver=0  order by posttime desc) where rownum <=1000) where status=0;

pg:
select count(*) from ( select * from 
(select * from sns_pclady.msg_message where receiver= 6499175  and  delbyreceiver=0  order by posttime desc) a limit 1000) b where b.status=0;
}}}
* 外连接写法调整
{{{
oracle:
select a.field1, b.field2 from a, b where a.item_id = b.item_id(+)

pg:
select a.field1, b.field2 from a left outer join b on a.item_id = b.item_id
}}}
* sequence写法调整
{{{
oracle:
sequence_name.nextval

pg:
nextval('sequence_name') 
}}}
* sysdate写法调整
{{{
oracle:
select sysdate from dual

pg:
select [db_sysdate] from dual
}}}
* decode写法调整
{{{
oracle:
decode(expr, search, expr[, search, expr...] [, default])

pg:
CASE WHEN expr THEN expr [...] ELSE expr END
CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END
}}}
* nvl写法调整
{{{
oracle:
NVL(hire_date, SYSDATE)

pg:
coalesce(expr1, expr2, expr3,....)  //这个是否可以代替decode？
}}}
* connect by 写法pg不支持
{{{
oracle:
pg:
WITH RECURSIVE source (part_no) AS (
SELECT 2
UNION ALL
SELECT part.part_no
FROM source JOIN part ON (source.part_no = part.parent_part_no)
)
SELECT * FROM source;
}}}

== 驱动 ==
* pg 里没有rollback 语句，不会自动rollback ，需要应用里触发异常。 
* 缺省返回的整型是Long
{{{
oracle:
BigDecimal count = (java.math.BigDecimal?)em.createNativeQuery("select count(*) from table").getSingleResult();

pg:
Long count = (Long)em.createNativeQuery("select count(*) from table").getSingleResult();
}}}