Oracle

oracle大数据类型 PostgreSQL和Oracle的数据类型的对比系列四

PostgreSQL是世界上功能最强大的开源数据库,在国内得到了越来越多机构和开发者的青睐和应用。随着PostgreSQL的应用越来越广泛,Oracle向PostgreSQL数据库的数据迁移需求也越来越多。数据库之间数据迁移的时候,首先遇到的,并且也是最重要的,就是数据类型之间的转换。下面根据自己的理解和测试,写了一些数据类型之间的差异以及迁移时的注意事项的文章,不足之处,尚请多多指教。

大数据类型

Oracle的大数据类型主要包括三类。分别是存储在数据库内部的类型,包括BLOB, CLOB, NCLOB。存储在外部的类型, 就是BFILE。还有Oracle不推荐使用, 仅用于特殊环境的类型。包括为了兼容老版本而使用的Long和为了数据在不同系统间移动而利用的Long raw类型。
PostgreSQL的大数据类型只有两种,就是存储二进制数据的bytea和存储字符类型的text。下面介绍一下它们之间的对应和迁移时的一些注意事项。

注意: PostgreSQL对应的大数据类型还有一个对象标识符类型(oid)。它是一个标识符,指向在pg_largeobject 系统表中的一个bytea类型的对象。由于它是用一个四字节的无符号整数实现,不能够提供大数据库范围内的唯一性保证。因此,postgreSQL不推荐使用oid类型。加上它的内部实现,也是使用bytea类型,所以就不单独介绍了。

1、存储在数据库内部的类型 1.1、BLOB类型

Oracle的Blob类型主要内容是二进制的大对象。最大长度是(4G-1)*database block size。在PostgreSQL中,与之对应的是bytea。最大长度是1G。虽然最大长度小于Blob,但是在实际应用中已经足够了。

Oracle BLOB

SQL> create table o_test(value blob);
表已创建。
SQL> insert into o_test values('867814324901abedf4314312');
已创建 1 行。
SQL> insert into o_test values('867814324901abedf4314312t');
insert into o_test values('867814324901abedf4314312t')
*
第 1 行出现错误:
ORA-01465: 无效的十六进制数字
SQL> select * from o_test;
VALUE
--------------------------------------------------------------------------------
867814324901ABEDF4314312

PostgreSQL bytea

"js">postgres=# create table p_test(value bytea);
CREATE TABLE
postgres=# insert into p_test values('867814324901abedf4314312');
INSERT 0 1
postgres=# insert into p_test values('867814324901abedf4314312t');
INSERT 0 1
postgres=# select * from p_test;
value
------------------------------------------------------
\x383637383134333234393031616265646634333134333132
\x38363738313433323439303161626564663433313433313274
(2 行记录)
1.2、CLOB类型

Oracle的Clob类型,主要存储基于数据库字符集的单字节或多字节文本信息,最大长度是(4G-1)*database block size。PostgreSQL中,可以使用text来对应。text的最大长度是1G,比Oracle的小。但是,实际应用中,1G已经足够。

Oracle CLOB

SQL> create table o_test( value clob);
表已创建。
SQL> insert into o_test values('122334543543666345435313421');
已创建 1 行。
VALUE
--------------------------------------------------------------------------------
122334543543666345435313421

PostgreSQL text

"js">postgres=# create table p_test(value text);
CREATE TABLE
postgres=# insert into p_test values('1234567890123');
INSERT 0 1
postgres=# select * from p_test;
value
---------------
1234567890123
(1 行记录)
1.3、NCLOB类型

Oracle的NClob类型,主要存储固定长度的UNICODE字符串,最大长度是(4G-1)*database block size。PostgreSQL中,可以使用text来对应。text的最大长度是1G,比Oracle的小。但是,实际应用中,1G已经足够。
Oracle CLOB

SQL> create table o_test1(value NCLOB);
表已创建。
SQL> insert into o_test1 values('1223344452525341');
已创建 1 行。
SQL> select DBMS_LOB.GETLENGTH(value) from o_test1;
DBMS_LOB.GETLENGTH(VALUE)
-------------------------
16
SQL> select * from o_test1;
VALUE
--------------------------------------------------------------------------------
1223344452525341

PostgreSQL text

"js">postgres=# create table p_test(value text);
CREATE TABLE
postgres=# insert into p_test values('1234567890123');
INSERT 0 1
postgres=# select * from p_test;
value
---------------
1234567890123
(1 行记录)
2、存储在数据库外部的类型 2.1、BFILE类型

存储在数据库外部的类型,就是指BFILE类型。BFILE是一种特殊的数据类型。数据并不保存在数据库内。数据库内仅仅是一个文件的指针。指明了文件的路径和文件名。实际的文件存储在硬盘上。只能对文件进行读取,而且不能Commit和Rollback。Oracle需要对文件所在的路径有读权限。文件的大小理论上可以达到2^64-1字节。
PostgreSQL中,没有储存在外部的数据类型。可以使用bytea来对应BFILE类型。数据类型的最大长度,没有BFILE的大。但是实际应用中,bytea的大小已经足够了。

Oracle BFILE

SQL> create table o_test(value bfile);
表已创建。
SQL> begin
2 insert into o_test values(bfilename('BFILE1', '00.txt'));
3 end;
4 /
PL/SQL 过程已成功完成。
SQL> declare txt1 BFILE;
2 piece raw(100);
3 amount binary_integer :=100;
4 begin
5 select value into txt1 from o_test;
6 DBMS_LOB.OPEN(txt1);
7 DBMS_LOB.READ(txt1, amount, 1, piece);
8 DBMS_LOB.CLOSE(txt1);
9 DBMS_OUTPUT.PUT_LINE(RAWTOHEX(piece));
10 end;
11 /
313233343535363635343333
PL/SQL 过程已成功完成。

PostgreSQL bytea

"js">postgres=# create table p_test(value bytea);
CREATE TABLE
postgres=# insert into p_test values('123455665433');
INSERT 0 1
postgres=# select * from p_test;
value
----------------------------
\x313233343535363635343333
(1 行记录)
3、Oracle不推荐使用的大数据类型 3.1、LONG RAW类型

Oracle的long raw主要用于不同的系统之间转移数据的时候所用,是Oracle不推荐使用的数据类型。Oracle推荐使用Blob来代替Long raw,并且Long raw的数据可以直接转换成Blob类型。Long raw的最大范围是2G。
PostgreSQL中,与之对应的就是bytea类型。

3.2、LONG类型

Oracle的Long类型是Oracle不推荐使用的一个数据类型。使用它有很多的限制。比如一个表中只能有1个字段,不能用在where条件中使用等等。Oracle保留它的目的只是为了兼容以前的版本。Long类型的最大范围是2^31 -1(即2G-1)个字节。
在PostgreSQL中,Long类型的对应字段是text。虽然最大字符数少于Long类型,但是,text的最大长度1G在实际应用中已经足够了。

Oracle LONG

SQL> create table o_test(value long);
表已创建。
SQL> insert into o_test values('12sdfadsfewr34qdfsdsvvatearaewra');
已创建 1 行。
SQL> select * from o_test;
VALUE
--------------------------------------------------------------------------------
12sdfadsfewr34qdfsdsvvatearaewra
SQL> select * from o_test where value ='12sdfadsfewr34qdfsdsvvatearaewra';
select * from o_test where value ='12sdfadsfewr34qdfsdsvvatearaewra'
*
第 1 行出现错误:
ORA-00997: 非法使用 LONG 数据类型

PostgreSQL text

"js">postgres=# create table p_test(value text);
CREATE TABLE
postgres=# insert into p_test values('12sdfadsfewr34qdfsdsvvatearaewra');
INSERT 0 1
postgres=# select * from p_test;
value
----------------------------------
12sdfadsfewr34qdfsdsvvatearaewra
(1 行记录)

Oracle的大数据类型虽然多一些,但是PostgreSQL中基本上就是text和bytea两个,分别对应文本信息和二进制信息。迁移的时候,文本信息转成text,二进制信息转成bytea。特殊类型BFILE形式的,可以额外写一些代码把数据从文件中读出转换成bytea。这样就可以完成大数据类型的迁移。