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();