《卸甲筆記》-PostgreSQL和Oracle的資料型別的對比系列五：其它型別

PostgreSQL是世界上功能最強大的開源資料庫，在國內得到了越來越多機構和開發者的青睞和應用。隨著PostgreSQL的應用越來越廣泛，Oracle向PostgreSQL資料庫的資料遷移需求也越來越多。資料庫之間資料遷移的時候，首先遇到的，並且也是最重要的，就是資料型別之間的轉換。下面根據自己的理解和測試，寫了一些資料型別之間的差異以及遷移時的注意事項的文章，不足之處，尚請多多指教。

其它型別

Oracle的內建資料型別中，還有一些其它的型別。不能夠歸類到字元、數字、時間和大資料型別等。比如RAW， ROWID， UROWID等。在資料遷移的時候，在PostgreSQL中也有與之相對應的型別。

1、RAW型別

Oracle的RAW型別主要用於不同的系統之間轉移資料的時候。用於儲存位串，以位元組為單位。作為資料庫列的資料型別的時候，最大位數為2000（作為變數的時候，最大位數為32767。因為現在談論的是資料遷移，所以作為變數的情況就不討論了）。

PostgreSQL中，可以使用bytea來對應RAW型別。

Oracle RAW

SQL> create table o_test(value raw(2000));

表已建立。

SQL> create table o_test2(value raw(2001));
create table o_test2(value raw(2001))
                               *
第 1 行出現錯誤:
ORA-00910: 指定的長度對於資料型別而言過長

SQL> insert into o_test values(utl_raw.cast_to_raw(`Hello! This is John`));

已建立 1 行。

SQL> select * from o_test;

VALUE
--------------------------------------------------------------------------------
48656C6C6F212054686973206973204A6F686E

PostgreSQL bytea

postgres=# create table p_test(value bytea);
CREATE TABLE
postgres=# insert into p_test values(cast(`Hello! This is John` as bytea));
INSERT 0 1
postgres=# select * from p_test;
                  value
------------------------------------------
 x48656c6c6f212054686973206973204a6f686e
(1 行記錄)

2、ROWID型別

Oracle的ROWID型別代表一條記錄在資料庫中的實體地址。使用BASE64編碼的字串表示的實體地址。固定是18位。ROWID是Oracle中的偽列，也可以定義一個ROWID型別的列，但是必須使用者自己保證該列的資料有效性。
PostgreSQL中，可以使用char(18)來遷移物件。PostgreSQL中還有一個CTID資料型別， 指的是一條記錄位於哪個資料塊的哪個位移上面。作用和ROWID類似。但是用的是數字對，資料遷移時，不能夠使用它對Oracle的ROWID進行遷移。

Oracle ROWID

SQL> create table o_test(value1 int, value2 rowid);

表已建立。

SQL> insert into o_test values(1, `1234567890ABCDEFAB`);

已建立 1 行。

SQL> insert into o_test values(2, `1234567890ABCDEFABC`);
insert into o_test values(2, `1234567890ABCDEFABC`)
                             *
第 1 行出現錯誤:
ORA-01410: 無效的 ROWID

SQL> select * from o_test;

    VALUE1 VALUE2
---------- ------------------
         1 B23456AM9AABCDEFAB

SQL> select t.rowid, t.* from o_test t;

ROWID                  VALUE1 VALUE2
------------------ ---------- ------------------
AAASQiAAEAAAAJ8AAA          1 B23456AM9AABCDEFAB

PostgreSQL char(18)

postgres=# create table p_test(value1 int , value2 char(18));
CREATE TABLE
postgres=# insert into p_test values(1, `B23456AM9AABCDEFAB`);
INSERT 0 1
postgres=# insert into p_test values(1, `AAASQiAAEAAAAJ8AAA`);
INSERT 0 1
postgres=# select * from p_test;
 value1 |       value2
--------+--------------------
      1 | B23456AM9AABCDEFAB
      1 | AAASQiAAEAAAAJ8AAA
(2 行記錄)
postgres=# select ctid, * from p_test;
 ctid  | value1 |       value2
-------+--------+--------------------
 (0,1) |      1 | B23456AM9AABCDEFAB
 (0,2) |      1 | AAASQiAAEAAAAJ8AAA
(2 行記錄)

3、UROWID型別

Oracle的UROWID型別支援Oracle的物理ROWID和邏輯ROWID，在索引組織表(Index Organization Table,簡稱為IOT)中的ROWID，就是邏輯ROWID。並且支援非Oracle資料庫的表的ROWID。也就是支援所有型別的ROWID。最大長度是4000。
PostgreSQL中，可以使用varchar來遷移物件。

Oracle UROWID

SQL> create table o_test(value1 int, value2 urowid);

表已建立。

SQL> insert into o_test values(1,`AAASQiAAEAAAAJ8AAA`);

已建立 1 行。

SQL> select value2 from o_test;

VALUE2
--------------------------------------------------------------------------------
AAASQiAAEAAAAJ8AAA

SQL> create  table o_test2(id number primary key) organization index;

表已建立。

SQL> insert into o_test2 values(`12345`);

已建立 1 行。

SQL> insert into o_test2 values(`12345989873827276839302`);

已建立 1 行。

SQL> set numw 25
SQL> select t.rowid, t.* from o_test2 t;

ROWID                                                            ID
----------------------------------------- -------------------------
*BAEAAjMEwwIYLv4                                              12345
*BAEAAjMNzAIYLmNjSlNJTVReA/4                12345989873827276839302

PostgreSQL VARCHAR

postgres=# create table p_test(value varchar);
CREATE TABLE
postgres=#  insert into p_test values(`AAASQiAAEAAAAJ8AAA`);
INSERT 0 1
postgres=#  insert into p_test values(`*BAEAAjMEwwIYLv4`);
INSERT 0 1
postgres=#  insert into p_test values(`*BAEAAjMNzAIYLmNjSlNJTVReA/4`);
INSERT 0 1
postgres=# select value,length(value) from p_test;
            value             | length
------------------------------+--------
 AAASQiAAEAAAAJ8AAA           |     18
 *BAEAAjMEwwIYLv4             |     16
 *BAEAAjMNzAIYLmNjSlNJTVReA/4 |     28
(3 行記錄)

Oracle的build-in型別裡面的這幾種特殊的資料型別，在資料遷移中是比較簡單的。在向PostgreSQL資料庫進行資料遷移的時候只要選對資料型別，應該就可以正確的遷移過來。

參考文件：

PostgreSQL 9.4.4 中文手冊：字元型別，二進位制型別，物件識別符號型別
http://www.postgres.cn/docs/9.4/datatype-binary.html (二進位制型別）
http://www.postgres.cn/docs/9.4/datatype-character.html (字元型別)
Database SQL Language Reference：Data Types
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#i45441