本文共 5442 字，大约阅读时间需要 18 分钟。
PostgreSQL是世界上功能最强大的开源数据库，在国内得到了越来越多机构和开发者的青睐和应用。随着PostgreSQL的应用越来越广泛，Oracle向PostgreSQL数据库的数据迁移需求也越来越多。数据库之间数据迁移的时候，首先遇到的，并且也是最重要的，就是数据类型之间的转换。下面根据自己的理解和测试，写了一些数据类型之间的差异以及迁移时的注意事项的文章，不足之处，尚请多多指教。
Oracle的大数据类型主要包括三类。分别是存储在数据库内部的类型，包括BLOB, CLOB, NCLOB。存储在外部的类型, 就是BFILE。还有Oracle不推荐使用， 仅用于特殊环境的类型。包括为了兼容老版本而使用的Long和为了数据在不同系统间移动而利用的Long raw类型。
PostgreSQL的大数据类型只有两种，就是存储二进制数据的bytea和存储字符类型的text。下面介绍一下它们之间的对应和迁移时的一些注意事项。注意： PostgreSQL对应的大数据类型还有一个对象标识符类型（oid）。它是一个标识符，指向在pg_largeobject 系统表中的一个bytea类型的对象。由于它是用一个四字节的无符号整数实现，不能够提供大数据库范围内的唯一性保证。因此，postgreSQL不推荐使用oid类型。加上它的内部实现，也是使用bytea类型，所以就不单独介绍了。
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 TABLEpostgres=# insert into p_test values('867814324901abedf4314312');INSERT 0 1postgres=# insert into p_test values('867814324901abedf4314312t');INSERT 0 1postgres=# select * from p_test; value------------------------------------------------------ \x383637383134333234393031616265646634333134333132 \x38363738313433323439303161626564663433313433313274(2 行记录) 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 TABLEpostgres=# insert into p_test values('1234567890123');INSERT 0 1postgres=# select * from p_test; value--------------- 1234567890123(1 行记录) Oracle的NClob类型，主要存储固定长度的UNICODE字符串，最大长度是(4G-1)*database block size。PostgreSQL中，可以使用text来对应。text的最大长度是1G，比Oracle的小。但是，实际应用中，1G已经足够。
Oracle CLOBSQL> 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)------------------------- 16SQL> select * from o_test1;VALUE--------------------------------------------------------------------------------1223344452525341 PostgreSQL text
postgres=# create table p_test(value text);CREATE TABLEpostgres=# insert into p_test values('1234567890123');INSERT 0 1postgres=# select * from p_test; value--------------- 1234567890123(1 行记录) 存储在数据库外部的类型，就是指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 /313233343535363635343333PL/SQL 过程已成功完成。 PostgreSQL bytea
postgres=# create table p_test(value bytea);CREATE TABLEpostgres=# insert into p_test values('123455665433');INSERT 0 1postgres=# select * from p_test; value---------------------------- \x313233343535363635343333(1 行记录) Oracle的long raw主要用于不同的系统之间转移数据的时候所用，是Oracle不推荐使用的数据类型。Oracle推荐使用Blob来代替Long raw，并且Long raw的数据可以直接转换成Blob类型。Long raw的最大范围是2G。
PostgreSQL中，与之对应的就是bytea类型。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--------------------------------------------------------------------------------12sdfadsfewr34qdfsdsvvatearaewraSQL> 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 TABLEpostgres=# insert into p_test values('12sdfadsfewr34qdfsdsvvatearaewra');INSERT 0 1postgres=# select * from p_test; value---------------------------------- 12sdfadsfewr34qdfsdsvvatearaewra(1 行记录) Oracle的大数据类型虽然多一些，但是PostgreSQL中基本上就是text和bytea两个，分别对应文本信息和二进制信息。迁移的时候，文本信息转成text，二进制信息转成bytea。特殊类型BFILE形式的，可以额外写一些代码把数据从文件中读出转换成bytea。这样就可以完成大数据类型的迁移。
参考文档：
PostgreSQL 9.4.4 中文手册：字符类型，二进制类型，对象标识符类型
（字符类型） (二进制类型） （对象标识符类型）Database SQL Language Reference：Data Types转载地址：http://tztwo.baihongyu.com/