《卸甲笔记》-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

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

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

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

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

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。这样就可以完成大数据类型的迁移。

参考文档：

PostgreSQL 9.4.4 中文手册：字符类型，二进制类型，对象标识符类型
http://www.postgres.cn/docs/9.4/datatype-character.html （字符类型）
http://www.postgres.cn/docs/9.4/datatype-binary.html (二进制类型）
http://www.postgres.cn/docs/9.4/datatype-oid.html （对象标识符类型）
Database SQL Language Reference：Data Types
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#i45441