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 ，需要应用里触发异常。

  1.使用JPA,方法声明为@Transactional自动回滚
  2.使用JPA，手动方式进行回滚
  EntityManagerFactory emf = ((EntityManagerFactory)EnvUtils.getEnv().getApplicationContext().getBean("entityManagerFactory"));
  EntityManager em = emf.createEntityManager();
  EntityTransaction et = em.getTransaction();
  try {
    et.begin();//事务开始
    em.createNativeQuery(sql).executeUpdate();
    em.createNativeQuery(sql).executeUpdate();
    et.commit();
  } catch (Exception e) {
    et.rollback();
  }
  3.使用JDBC
  DataSourceTransactionManager tm = new DataSourceTransactionManager(((DataSource) EnvUtils.getEnv().getApplicationContext().getBean("dataSource")));
  TransactionTemplate tt = new TransactionTemplate(tm);
  final JdbcTemplate jt = new JdbcTemplate(((DataSource) EnvUtils.getEnv().getApplicationContext().getBean("dataSource")));
  tt.execute(new TransactionCallback() {
      public Object doInTransaction(TransactionStatus ts) {
          jt.execute(sql);
          jt.execute(sql);
          return null;
      }
  });
  

• 缺省返回的整型是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();
  

• like的写法调整  http://bbs.pconline.cn/topic-2071.html

  oracle:
  select * from ENT_CONSTANTS where lower(type) like '%config.%'
  
  pg:
  select * from ENT_CONSTANTS where lower(type) like '%config.%' escape '\\'  
  或者 select * from ENT_CONSTANTS where lower(type) like '%config.%' escape E'\\'  
  注意这个E 是pg 里escape 的语法。 这样基本的问题就解决了。 
  或者 select * from ENT_CONSTANTS where  type  ~*  E'config.'  
  
  至少不用对每行都做lower（）的函数运算了。 ~  ~*  是pg 里正则表达的运算符  ~* 不区分大小写
  

导数据

• 字符长度超长

  [postgres@test740-10 ora2cyp]$ cat export.log |grep ERROR
  psql:ENT_COMPANY_WEB.sql:142: ERROR: value too long for type character varying(720)
  psql:ENT_NEWS_WEB.sql:19: ERROR: value too long for type character varying(500)
  数据长度不对，有可能跟转为utf8 字符集，长度增加有关系，奇怪的是其他表不需要。
  
  psql:ENT_QQ_CONTACT.sql:21: ERROR: bigint out of range
  其中的qq 号明显有问题，修改字段类型，重新导入，建议开发对输入做限制。
  
  psql:ENT_QUESTION.sql:1125: ERROR: value too long for type character varying(2000)
  ENT_REQUEST_ERROR.sql Tue Mar 13 21:19:13 CST 2012
  psql:ENT_REQUEST_ERROR.sql:43: ERROR: value too long for type character varying(4000)
  psql:ENT_SYSLOG.sql:72: ERROR: value too long for type character varying(500)
  
  数据长度不对，有可能跟转为utf8 字符集，长度增加有关系，奇怪的是其他表不需要。
  

• 字符编码问题，部分gbk了里的乱码，没有对应utf8编码，无法转入，需要人工干预。  http://bbs.pconline.cn/topic-1954.html