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