wiki:v6/pgsummary

Context Navigation

Version 19 (modified by huangzhong, 14 years ago) (diff)
--

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 还是很能干的，果然找到了一个替代方案
1	cyp_app=> \df STRING_AGG
2	                            List of functions
3	   Schema   |    Name    | Result data type | Argument data types | Type
4	------------+------------+------------------+---------------------+------
5	 pg_catalog | string_agg | text             | text, text          | agg
6	(1 row)

http://bbs.pconline.cn/topic-2159.html

驱动

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 = ?";

createNativeQuery sql中参数对应的类型和传入的类型必须一致：

String stmt = "select a.* from v_ent_product a, ent_company b " + " where a.user_id=b.id " + " and a.product_id=?1"
        + " and a.sell_status=" + CompanyProduct.SELLER_STATUS_UP + " and a.status<="
        + CompanyProduct.CONTENT_STATUS_NORMAL + " and b.status>=" + Company.STATUS_NO_AUDIT + " and b.vip_rank>="
        + Company.VIP_RANK_FREE + " and b.id IN(" + inStr.toString() + ") ";
...
query.setParameter( i + 2, Integer.parseInt(companyIds[i]) );//Oracle 版本的这里不需要转换

导数据

字符长度超长

[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 脚本已经解决这个问题，已经通过了测试。

ent_question 表 question_contact 字段存贮有二进制数据

question_contact 字段 存有二进制数据00  ， 在文本下表现为可打印字段^@ (linux  ctl+v+2) 
@ 在pg里是运算符导致问题？ 
pg报错： ERROR:  VALUES lists must all be the same length at character 16690

初步查明，这些数据都为历史数据，新数据已经没有这个字段的输入了。
 
详细描述： 
http://bbs.pconline.cn/topic-2164.html

oracle中的number类型数据导入postgresql

用xdb 从oracle导出来的是文本，转入pg的时候，会发生自动转换
number类型导入到postgresql中会转为bigint类型，number(10,2)这样的会转为double类型，造成数据不一致

解决方案：批量生成表结构，然后手工修正，修正的规则是
number类型的根据业务逻辑判断转换后的应该是bigint还是numeric(10,2)
number(10,2)类型的转换为numeric(10,2)

Download in other formats:

Plain Text