Oracle数据库迁移到PostgreSQL的问题总结

sakuraMonster

已于 2024-06-07 18:25:48 修改

阅读量7.9k

点赞数 26

分类专栏： Java 文章标签： Oracle PostgreSQL Java pg

于 2019-09-23 16:51:43 首次发布

本文链接：https://blog.csdn.net/sinat_16611549/article/details/101212900

版权

Java 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

PostgreSQL特点：

1. PostgreSQL 可以在所有主要操作系统中运行；
2. PostgreSQL支持文本、图像、声音和视频；
3. 支持SQL的基本功能，例如：复杂SQL查询，子选择，外键，触发器，视图，事务，多进程并发控制（MVCC），流式复制（9.0），热备（9.0）
4. 在PostgreSQL中，表可以设置为从 ‘父’表继承其特征；
5. 可以安装多个扩展以向PostgreSQL添加附加功能；

PostgreSQL常用工具：

1. psql: 命令行工具，也是管理PostgreSQL的主要工具；
2. pgAdmin 是免费开源图形用户界面管理工具；
3. pgFouine: 日志分析器，可以从PostgreSQL日志文件创建报告。

1. 数据库切换中出现的差别记录

不能建与表同名的索引。
```
 create index store on store; ×
```
支持boolean类型，可以存储T/F, TRUE/FALSE/NULL。
不支持clob类型和blob类型。在oralce中， clob字段的数据与记录不是存在一起，它会存储数据的指针（long类型），相当于是内存地址；在PostgreSQL中，可以使用text类型存储。
数据库字段类型为integer时，对应的jdbc查询类型也是Integer（oracle中查询类型是BigDecimal）; 使用聚合函数count(1)时的返回值类型为BigInteger(oracle中查询类型为BigDecimal)。

postgreSQL与oracle中函数的使用差异：

oracle	postgreSQL	描述
bitant(a, b)	a & b	按位与运算
nvl(a, 0)	coalesce(a, 0)	判空
to_number(int)	to_number(int, text) 例如: to_number(123, "999999") : text表示精度	转成数值类型
to_char(int)	to_char(int, text) 例如 to_char(123, "999999") : text表示精度	转成字符类型
to_date(text)	to_date(text, text)	格式化日期
sysdate	current_date	当前时间
instr('great', 'eat)	positon('eat' IN 'great')	字符串中包含字符
序列名.nextval	nextval(序列名）	序列的下一个取值

注意：要保留时分秒时用to_timestamp(text, text)
例如：

insert into monthsettle(no,startdate,finishdate,holder,settler,settletime)
values(200301,to_timestamp('2003.1.1 0:00:01','yyyy.mm.dd hh24:mi:ss'),to_timestamp('2003.1.25 23:59:59','yyyy.mm.dd hh24:mi:ss'),'','',null);

左连接和右连接（使用标准写法）

 oracle: 左连接 : a.id = b.id(+) ;
         右连接 : a.id(+) = b.id

 postgreSQL: 左连接：a left join b on a.id = b.id
             右连接：a right join b on a.id = b.id

系统隐藏字段ctid, 是每行数据在表中一个物理标识符，和oracle的rowid类似；有一点不同，当表被vacuumfull或该行值被update时该值会被改变。
oracle使用rownum分页, postgreSQL使用limit.

decode函数：
oracle：

decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
   该函数的含义如下：
IF 条件=值1 THEN
　　　　RETURN(翻译值1)
ELSIF 条件=值2 THEN
　　　　RETURN(翻译值2)
　　　　......
ELSIF 条件=值n THEN
　　　　RETURN(翻译值n)
ELSE
　　　　RETURN(缺省值)
END IF

decode(字段或字段的运算，值1，值2，值3）
    这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3
 当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多

postgreSQL:

 Select CASE
      WHEN foo = 'hi'   THEN 'there'
      WHEN foo = 'good' THEN 'bye'
      ELSE 'default'
  END

查询时别名使用关键字时需要用双引号（“”）
例如： select findex index from dual;
index为sql关键字，所以需要改成： select findex \"index\"(将双引号转义）
使用QueryDefition时字段类型需要严格对应，比如： integer类型就需要传integer; String类型传String （QueryDecoder里面的参数需要进行转换）
例如：
```
   QueryDefinition def = new QueryDefinition();
   def.addCondition("storeGid", Integer.valueOf(user.getUuid()));
```
当storeGid字段数据类类型为integer时，需要转成对应的数据类型传入，不然会报错(integer == varying)。
自动生成uuid方法
需要安装扩展程序： create extension "uuid-ossp";(只用安装一次）
oracle： sys_uuid();
postgreSQL： uuid_generate_v1()、 uuid_generate_v4()

分页查询时使用NativeBigInQueryExecutor，不使用NativeQueryExecutor。

    NativeBigInQueryExecutor queryExecutor = new NativeBigInQueryExecutor(getEm(), sq, fields);
    QueryResult<WholeSaleBck> qr = queryExecutor.query(definition.getPage(),
        definition.getPageSize(), WholeSaleBck.class);

虚表dual问题： pgsql没有dual虚拟表，为保证兼容性，可以创建伪视图代替，并赋予权限。

   create or replace view dual as 
    select NULL::"unknown" where 1=1;
    alter table dual owner to postgres;
    grant all on table dual to postgres;

    grant select on table dual to public;

子查询： pgsql必须有别名

 select count(1) from (select * from store) s;

Oracle中的"" 和NUll是相同的，但是postgreSQL不同，所以当插入空值时需要修改为NULL.

  insert into store (name) values ('');
  insert into store (name) values (null);
 
 第一种方式获取的值并不等于null.

字符串连接符 || ， Oracle中 "a" || null 结果为 "a"; 而pgsql中结果为NULL。所以使用concat（）函数代替。但是pgsql没有concat方法，所以可以创建函数concat代替；

create or replace function concat(text, text)
     returns text as
   $body$select coalesce($1,'') || coalesce($2,'')$body$
   language 'sql' volatile;
    alter function concat(text, text) owner to postgres;

用关键字作为字段名的字段需要区分大小写, 使用sql脚本执行的表名和字段名默认都为小写，使用客户端生成的表如果有大写，将会带上双引号，例如： “ABc”

create table abc (
   username  varchar(30),
   type  varchar(30),
   id integer
);
select * from abc; select * from ABC; (查询的表都为abc表)
select * from "ABc"; (查询的表为ABc表)

关键字： type，
使用双引号时
INSERT INTO ABC ("TYPE") VALUES ("ab"）; ×
INSERT INTO ABC ("type") VALUES ("ab");  √

pgsql不支持procedure和package，都需要改写成function。当package有全局变量的情况修改起来比较麻烦，我们是用临时表传递的。
oracle没有继承和重载特性，pgsql支持继承和函数重载.
jdbc差异：
Oracle的jdbc连接字符串：db.url=jdbc:oracle:thin:@192.168.1.1:1521:ORCL
Postgresql的连接字符串：db.url=jdbc:postgresql:@192.168.1.1:5432/database

PostgreSQL不支持update的别名语法

update fauser o set o.note='xxxx';

提示报错：
ERROR:  column "o" of relation "fauser" does not exist
LINE 1: update fauser o set o.note='xxxx' 
                            ^
SQL state: 42703
Character: 21

2. clob类型的处理

问题背景

当字段为clob/blob类型时，实体类中对应的属性增加注解@Lob，该属性用String/Clob/Character/Blob/Byte等类型作为返回类型。
oracle数据库对该属性处理是在getClob方法调用了getLong，然后试图将这个CLob转换成Long。
postgreSQL不支持clob类型，使用text存储大数据。

分析过程

1. hibernate根据查询的结果集转化成对应实体类时出现的类型错误。
AbstractEntityPersister.hydrate()加载数据结果集：

   final ResultSet propertyResultSet = propertyIsDeferred ? sequentialResultSet : rs;
   final String[] cols = propertyIsDeferred ? propertyColumnAliases[i] : suffixedPropertyColumns[i];
   values[i] = types[i].hydrate( propertyResultSet, cols, session, object );

其中cols是实体类的每个属性值， types是每个属性对应的type；
再根据每个属性的type找到BasicType，去设置对应的值。
例如：
```
 public class User {
   private String username;
   private Integer password; 
}
```
User类定义的属性username为String, 对应hibernate在加载该实体为持久化对象时绑定的type就是默认的string, 对应的BasicType是StringType;

2. Hibernate解析type去赋值 (typesi.hydrate( propertyResultSet, cols, session, object )

 protected final T nullSafeGet(ResultSet rs, String name, WrapperOptions options) throws SQLException {
       return sqlTypeDescriptor.getExtractor( javaTypeDescriptor ).extract( rs, name, options );
 }

public AbstractStandardBasicType(SqlTypeDescriptor sqlTypeDescriptor, JavaTypeDescriptor<T> javaTypeDescriptor) {
     this.sqlTypeDescriptor = sqlTypeDescriptor;
     this.javaTypeDescriptor = javaTypeDescriptor;
}

通过上面源码可以看到：基本类型AbstractStandardBasicType有两个属性sqlTypeDescriptor 和 javaTypeDescriptor 。每个继承AbstractStandardBasicType类的子类都会给这两个参数赋予初始值。

当Hibernate对实体类的field赋值时，会根据field的类型（继承于AbtractStandardBasicType）去执行相应的赋值操作。
所以Hibernate在持久化实体类的时候就会对每个field确定一个基本类型，用来后续操作中的赋值。

3. Hibernate初始化实体类中标注有@Lob类型的字段处理
SimpleValueBinder类中绑定type

else if ( property.isAnnotationPresent( Lob.class ) ) {

		if ( mappings.getReflectionManager().equals( returnedClassOrElement, java.sql.Clob.class ) ) {
			type = "clob";
		}
		else if ( mappings.getReflectionManager().equals( returnedClassOrElement, java.sql.Blob.class ) ) {
			type = "blob";
		}
		else if ( mappings.getReflectionManager().equals( returnedClassOrElement, String.class ) ) {
			type = Hibernate.MATERIALIZED_CLOB.getName();
		}
		else if ( mappings.getReflectionManager().equals( returnedClassOrElement, Character.class ) && isArray ) {
			type = Hibernate.WRAPPER_CHARACTERS_CLOB.getName();
		}
		else if ( mappings.getReflectionManager().equals( returnedClassOrElement, char.class ) && isArray ) {
			type = Hibernate.CHARACTERS_CLOB.getName();
		}
		else if ( mappings.getReflectionManager().equals( returnedClassOrElement, Byte.class ) && isArray ) {
			type = Hibernate.WRAPPER_MATERIALIZED_BLOB.getName();
		}
		else if ( mappings.getReflectionManager().equals( returnedClassOrElement, byte.class ) && isArray ) {
			type = Hibernate.MATERIALIZED_BLOB.getName();
		}

可以看到当实体类的java类型为String时，type = Hibernate.MATERIALIZED_CLOB.getName()，对应的基本类型是MaterializedClobType。

查看MaterializedClobType源码：

public MaterializedClobType() {
	this(
		ClobTypeDescriptor.DEFAULT,
		new AlternativeLobTypes.ClobTypes<String,MaterializedClobType>( MaterializedClobType.class )
	);
	}

	protected MaterializedClobType(SqlTypeDescriptor sqlTypeDescriptor,
								   AlternativeLobTypes.ClobTypes<String,MaterializedClobType> clobTypes) {
		super( sqlTypeDescriptor, StringTypeDescriptor.INSTANCE, clobTypes );
	}

	public String getName() {
		return "materialized_clob";
	}

由上述代码可以看到， MaterializedClobType中默认的sqlTypeDescriptor 是ClobTypeDescriptor.DEFAULT
从而让hibernate以ClobTypeDescriptor类型来处理clob类型的字段。

解决方案

定义EJB时，取消@Lob标注，按String对待；
定义EJB时，保留@Lob标注，增加@Type((type = "org.hibernate.type.TextType")注解；
在hibernate 4时，数据库方言基础类中支持替换sqlTypeDescriptor；可以重载PostgreSqlDialect类的remapSqlTypeDescriptor()方法，将Clob当成longvarchar处理。

例如：

     
@Override
public SqlTypeDescriptor remapSqlTypeDescriptor(SqlTypeDescriptor sqlTypeDescriptor)
 {
   switch (sqlTypeDescriptor.getSqlType())
{
   case Types.CLOB:
   return LongVarcharTypeDescriptor.INSTANCE;
   case Types.BLOB:
   return LongVarbinaryTypeDescriptor.INSTANCE;
}
   return super.remapSqlTypeDescriptor(sqlTypeDescriptor);
}

当前hibernate版本为3.6.8，且要同时支持oracle对clob的处理不冲突。
Dialect类中提供了addTypeOverride（BasicType typeOverride）方法。
这样就可以将MaterializedClobType类型进行重写，让其当成longvarchar处理。

   public static final StringClobType INSTANCE = new StringClobType(
      LongVarcharTypeDescriptor.INSTANCE, null);

  protected StringClobType(SqlTypeDescriptor sqlTypeDescriptor,
      AlternativeLobTypes.ClobTypes<String, MaterializedClobType> clobTypes) {
    super(sqlTypeDescriptor, clobTypes);
  }

  @Override
  public String getName() {
    return "materialized_clob";
  }

再重写数据库方言，注册重写的basicType.

  public PostgreSqlDialect() {
    super();
    addTypeOverride(StringClobType.INSTANCE);
  }

sakuraMonster

关注

26
点赞
踩
54

收藏

觉得还不错? 一键收藏
1
评论
Oracle数据库迁移到PostgreSQL的问题总结

PostgreSQL特点：1. PostgreSQL 可以在所有主要操作系统中运行；2. PostgreSQL支持文本、图像、声音和视频；3. 支持SQL的基本功能，例如：复杂SQL查询，子选择，外键，触发器，视图，事务，多进程并发控制（MVCC），流式复制（9.0），热备（9.0）4. 在PostgreSQL中，表可以设置为从 ‘父’表继承其特征；5. 可以安装多个扩展以向Pos...
复制链接

扫一扫