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

• 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;
  

• wm_concat写法调整

  oracle(默认的连接符为逗号):
  select wm_concat (distinct area_id) from pp_ad group by brand_id ;
  
  pg:
  select STRING_AGG (company_name, ',') from pp_ad group by brand_id ;
  select STRING_AGG (distinct area_id::text, ',') from pp_ad group by brand_id ;
  

驱动

• 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 里正则表达的运算符  ~* 不区分大小写
  

• PreparedStatement中参数的占位符不能有编号

  String sql = "select * from ent_company where id = ?1";
  PreparedStatement ps = con.prepareStatement(sql);
  ResultSet rs = ps.executeQuery();
  执行时报错，要把?后面的数字去掉，改成
  String sql = "select * from ent_company where id = ?";
  
  

导数据

• 字符长度超长

  [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 字符集，长度增加有关系，奇怪的是其他表不需要。
  
  '''  后记 '''
  
  问题已经解决，主要原因出在我们在oracle 转pg过程中，对元字符进行了转义，导致导致原本只有站占一个字符的元字符变成了多个字符导致字段长度改变
  目前这个问题已经解决，已经通过了测试。
  

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

  '''后记'''
  问题已经解决，采用xdb脚本来转数据。不在使用orapg 来转数据，只利用他来生成建库脚本。 
  
  

• oracle数据中的回车换行导到pg中的时候变成了\r\n

  '''后记'''
  问题解决， 因为xdb脚本中针对元字符\r\n 的转义，导致\r\n 进入pg后变成两个字符，java页面显示为\r\n 而不在是控制字符。
  新版本的xdb 脚本已经解决这个问题，已经通过了测试。