《卸甲筆記》-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