PostgreSQL和Oracle的数据类型的对比
2023-06-27 17:01:40 
阿炯
PostgreSQL和Oracle的数据类型的对比系列之
一：字符类型
二：数字类型
三：日期时间类型
四：大数据类型
五：其它类型
本文总结息《卸甲笔记》，感谢原作者。
--------------------------------------------------------------
一：字符类型
PostgreSQL是世界上功能最强大的开源数据库，在国内得到了越来越多机构和开发者的青睐和应用。随着PostgreSQL的应用越来越广泛，Oracle向PostgreSQL数据库的数据迁移需求也越来越多。数据库之间数据迁移的时候，首先遇到的，并且也是最重要的，就是数据类型之间的转换。下面根据自己的理解和测试，写了一些数据类型之间的差异以及迁移时的注意事项的文章，不足之处，尚请多多指教。
字符类型
大家知道，Oracle有四种字符类型，分别是char，varchar2，nchar，nvarchar2。而PostgreSQL只有两种字符类型，分别是character (char)和 character varying(varchar)。它们之间的有什么异同呢？
1、数据最小单位
Oracle：char和varchar2的数据最小单位，默认值为BYTE，即字节数。可以修改为CHAR，则为字符。通过参数NLS_LENGTH_SEMANTICS来控制。
而Oracle的nchar和nvarchar2是针对特定字符集来的，数据最小单位根据字符集不同而不同。如字符集是AL16UTF16，就是2个字节，而字符集是UTF8，就是3个，通过参数NLS_NCHAR_CHARACTERSET来控制（我的Oracle的NLS_NCHAR_CHARACTERSET值就是AL16UTF16，一个字为2byte）。
当数据不够最大长度的时候，char和nchar写入/取出的是最大长度的字符串，超出实际长度的使用空格补齐。而varchar2和 nvarchar2写入/取出的是实际操作的字符串。
SQL> create table o_test(id int primary key,
2 name char(5),
3 n_name nchar(5),
4 addr varchar2(5),
5 n_addr nvarchar2(5));
表已创建。
SQL> insert into o_test values(1, 'li', 'li', 'BJ', 'BJ');
已创建 1 行。
SQL> select lengthb(name), lengthb(n_name), lengthb(addr),lengthb(n_addr)
2 from o_test;
LENGTHB(NAME) LENGTHB(N_NAME) LENGTHB(ADDR) LENGTHB(N_ADDR)
------------- --------------- ------------- ---------------
5 10 2 4
PostgreSQL：char和 varchar的数据最小单位都是字符(CHAR)。
当数据不够最大长度的时候，char类型的数据物理上用空白填充到指定的长度，并且以这种方式存储和显示。但是，填充的空白是无意义的。在取出来实际使用的时候，空白会自动被删除。VARCHAR类型的数据，会按照实际长度存储。最后的空白都是有意义的，不会被删除。
highgo=# create table p_test(id smallint primary key,
highgo(# name char(5),
highgo(# v_name varchar(5));
CREATE TABLE
highgo=# insert into p_test values(1,'赵 ', '赵老大 ');
INSERT 0 1
highgo=# select length(name), length(v_name) from p_test;
length | length
--------+--------
1 | 4
(1 行记录)
2、数据类型的最大长度
Oracle：char类型的最大长度是2000字节，varchar2是4000个字节。
nchar，nvarchar2类型的最大长度根据数据集不同而不同。最终的byte数不能超过2000和4000。如字符集为AL16UTF16，则nchar的为2000/2=1000，而nvarchar2则为4000/2=2000
SQL> create table o_test2(name char(2001));
create table o_test2(name char(2001))
第 1 行出现错误:
ORA-00910: 指定的长度对于数据类型而言过长
SQL> create table o_test2(name char(2000));
表已创建。
SQL> create table o_test3(name varchar2(4001));
create table o_test3(name varchar2(4001))
第 1 行出现错误:
ORA-00910: 指定的长度对于数据类型而言过长
SQL> create table o_test3(name varchar2(4000));
表已创建。
SQL> create table o_table5(name nchar(1001));
create table o_table5(name nchar(1001))
第 1 行出现错误:
ORA-00910: 指定的长度对于数据类型而言过长
SQL> create table o_table5(name nchar(1000));
表已创建。
SQL> create table o_table6(name nvarchar2(2001));
create table o_table6(name nvarchar2(2001))
第 1 行出现错误:
ORA-00910: 指定的长度对于数据类型而言过长
SQL> create table o_table6(name nvarchar2(2000));
表已创建。
PostgreSQL：理论最大数据长度是1G，默认值是10 1024 1024=10485760（具体的值由htup_details.h的MaxAttrSize决定）
highgo=# create table p_test3(name char(10485761));
错误: 类型 char 的长度不能超过 10485760
第1行create table p_test3(name char(10485761));
^
highgo=# create table p_test3(name char(10485760));
CREATE TABLE
highgo=# create table p_test4(name varchar(10485761));
错误: 类型 varchar 的长度不能超过 10485760
第1行create table p_test4(name varchar(10485761));
^
highgo=# create table p_test4(name varchar(10485760));
CREATE TABLE
3、定义数据时的参数（字符数)
Oracle：char类型，不带字符数的时候，是1个字符（char(1)），而varchar2必须有字符数。
nchar和nvarchar2分别与char，varchar2类似。
SQL> create table o_test1(name char, addr varchar);
create table o_test1(name char, addr varchar)
第 1 行出现错误:
ORA-00906: 缺失左括号
SQL> create table o_test1(name char, addr varchar(5));
表已创建。
SQL> insert into o_test1 values('12','OK');
insert into o_test1 values('12','OK')
第 1 行出现错误:
ORA-12899: 列 "SCOTT"."O_TEST1"."NAME" 的值太大 (实际值: 2, 最大值: 1)
SQL> insert into o_test1 values('1','OK');
已创建 1 行。
PostgreSQL：char类型，不带字符数的时候，也是默认一个字符(char(1))，而varchar不带字符数的时候，没有限制。
highgo=# create table p_test1(name char, addr varchar);
CREATE TABLE
highgo=# insert into p_test1 values('12', '1234567890');
错误: 对于字符类型来说这个值太长了(1)
highgo=# insert into p_test1 values('1', '1234567890');
INSERT 0 1
4、插入数据库的时候的，字符数超出最大长度部分的处理。
Oracle：报错。即使超出的部分是字符末的空格。
SQL> insert into o_test1 values('1','123456');
insert into o_test1 values('1','123456')
第 1 行出现错误:
ORA-12899: 列 "SCOTT"."O_TEST1"."ADDR" 的值太大 (实际值: 6, 最大值: 5)
SQL> insert into o_test1 values('1','1234 ');
insert into o_test1 values('1','1234 ')
第 1 行出现错误:
ORA-12899: 列 "SCOTT"."O_TEST1"."ADDR" 的值太大 (实际值: 6, 最大值: 5)
PostgreSQL：当超出部分是有效的字符的时候，报错。
当是最后的空格时，截断为最大长度，不报错。但是当使用明确的类型转换为最大字符数的时候，截断为最大长度，不报错。
highgo=# create table p_test2(name char(2), addr varchar(5));
CREATE TABLE
highgo=# insert into p_test2 values('123', '12345');
错误:对于字符类型来说这个值太长了(2)
highgo=# insert into p_test2 values('12 ', '12345');
INSERT 0 1
highgo=# insert into p_test2 values(102 :: char(2) , '12345');
INSERT 0 1
highgo=# select * from p_test2;
name | addr
------+-------
12 | 12345
10 | 12345
(2 行记录)
字符类型数据迁移过程中，应该先根据Oracle数据的字符集和数据长度，从类型和长度上保证对方的数据能够完全的转换过来。再注意一些转换过程中SQL的写法，字符型的数据基本上就能够完全的、正确的迁移到PostgreSQL数据库中来了。
参考文档：
PostgreSQL 9.4.4 中文手册：8.3.字符类型
http://www.postgres.cn/docs/9.4/datatype-character.html
Database SQL Language Reference：Oracle Built-in Data Types
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#SQLRF30020
--------------------------------------------------------------
二：数字类型
数字类型
Oracle内建的数字类型有四种，分别是number，float，binary_float，binary_double。由于和其余数据库的数据兼容，而产生了smallint，int，Integer，Decimal等多种数字类型，其实内部都是使用内建的四种实现的。因此我们只讨论内建的四种数据类型。
PostgreSQL的数字类型有三类。分别是 整数类型，包括smallint，integer和bigint。任意精度类型，包括numeric和decimal两种(其中decimal等同于numeric)，以及浮点数类型，包括real和double。它们之间的区别和迁移时应该注意什么呢？下面按照精确类型(整数类型和任意精度类型)和非精确类型(浮点数类型)两类来进行讨论。
1、精确类型(整数和任意精度数值)
Oracle的number(p, s) 类型，根据精度(p)和小数位数(s)的不同，分别对应PostgreSQL中的多种情况。
1.1、当小数位数(s)=0时
当小数位数(s)=0时，可以对应PostgreSQL的smallint, integer, bigint, numeric(p)等多种数据类型。由于smallint，Integer，bigint的算术运算效率比numberic高的多，所以迁移时建议如下处理：
1.1.1、当p <= 4时，使用integer或者smallint
当p <= 4时，可以使用smallint和Integer，推荐使用Integer，因为它在取值范围、存储空间、性能之间最为平衡。只有磁盘空间紧张的情况下，才建议使用smallint。
Oracle number(p, 0) p <=4
SQL> create table o_test1( value number(4,0));
表已创建。
SQL> insert into o_test1 values(99999);
insert into o_test1 values(99999)
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度
SQL> insert into o_test1 values(9999);
已创建 1 行。
PostgreSQL smallint
postgres=# create table p_test1(value smallint);
CREATE TABLE
postgres=# insert into p_test1 values(99999);
错误: smallint 超出范围
postgres=# insert into p_test1 values(9999);
INSERT 0 1
1.1.2、当4 < p <= 9时，应该使用integer
Oracle number(p, 0) 4 < p <= 9
SQL> create table o_test1(value number(9,0));
表已创建。
SQL> insert into o_test1 values(9999999999);
insert into o_test1 values(9999999999)
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度
SQL> insert into o_test1 values(999999999);
已创建 1 行。
PostgreSQL integer
postgres=# create table p_test1(value integer);
CREATE TABLE
postgres=# insert into p_test1 values(9999999999);
错误: 整数超出范围
postgres=# insert into p_test1 values(999999999);
INSERT 0 1
1.1.3、当9 < p <= 18时，使用bigint
当9 < p <= 18时，这个时候integer的精度已经不够了，应该使用bigint
Oracle number(p, 0) 9 < p <= 18
SQL> create table o_test1(value number(18,0));
表已创建。
SQL> insert into o_test1 values(9999999999999999999);
insert into o_test1 values(9999999999999999999)
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度
SQL> insert into o_test1 values(999999999999999999);
已创建 1 行。
PostgreSQL bigint
postgres=# create table p_test1 (value bigint);
CREATE TABLE
postgres=# insert into p_test1 values(9999999999999999999);
错误: bigint 超出范围
postgres=# insert into p_test1 values(999999999999999999);
INSERT 0 1
1.1.4、当p > 18时，使用numeric(p, 0)
当p>18的时候，需要使用numeric(p, 0)。才能保证数据转换的精度。
Oracle number(p, 0) p > 18
SQL> create table o_test1(value number(19,0));
表已创建。
SQL> insert into o_test1 values(9999999999999999999);
已创建 1 行。
PostgreSQL numeric(19, 0)
postgres=# create table p_test1 (value numeric(19, 0));
CREATE TABLE
postgres=# insert into p_test1 values(9999999999999999999);
INSERT 0 1
1.2、当小数位数(s) > 0时
当小数位数(s) > 0时，由于有小数，只能够使用numeric(p, s)。
1.2.1、当0 < s <=p的时候，使用numeric(p, s)
Oracle number(p, s) 0 < s <= p
SQL> create table o_test1(value number(5,3));
表已创建。
SQL> insert into o_test1 values(123.567);
insert into o_test1 values(123.567)
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度
SQL> insert into o_test1 values(12.56789);
已创建 1 行。
SQL> select * from o_test1;
VALUE
----------
12.568
PostgreSQL numeric(p, s) p >= s
postgres=# create table p_test1( value numeric(5, 3));
CREATE TABLE
postgres=# insert into p_test1 values(123.456);
错误: 数字字段溢出
描述: 精度为5,范围是3的字段必须四舍五入到小于10^2的绝对值.
postgres=# insert into p_test1 values(12.456789);
INSERT 0 1
postgres=# select * from p_test1;
value
--------
12.457
(1 行记录)
1.2.2、当 p < s的时候，使用numeric(s, s)
当 p < s的时候，使用numeric(s, s)。此时，Oracle可以控制小数点后0的个数，而PostgreSQL不可。由于数据迁移，只需要把数据按照原来的样式迁移过来即可，所以不存在问题。
Oracle number(p, s) s > p
SQL> create table o_test1(value number(3,5));
表已创建。
SQL> insert into o_test1 values(0.056789);
insert into o_test1 values(0.056789)
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度
SQL> insert into o_test1 values(0.0056789);
已创建 1 行。
SQL> select * from o_test1;
VALUE
----------
.00568
PostgreSQL numeric(p, s) s > p
postgres=# create table p_test1( value numeric(5, 5));
CREATE TABLE
postgres=# insert into p_test1 values(0.056789);
INSERT 0 1
postgres=# insert into p_test1 values(0.0056789);
INSERT 0 1
postgres=# select * from p_test1;
value
---------
0.05679
0.00568
(2 行记录)
1.3、当小数位数(s) < 0时
当小数位数(s) < 0的时候，由于此时p代表精度的位数，s位决定了在何处进行位数的舍入。s < 0的时候，这种根据s来进行数据舍入的功能PostgreSQL尚不具备，但由于数据迁移时只要能将Oracle的数据完全转换过来就行了。所以对于数据迁移来说也不存在问题。
PostgreSQL中，小数位数必须大于等于0。此时的迁移，需要把p + |s| 作为数据的精度来判断。其余和s=0的时候相同。
1.3.1、当p + |s| <= 4时，使用integer或者smallint
当p+ |s| <= 4时，可以使用smallint和Integer，推荐使用Integer，只有磁盘空间紧张的情况下，才建议使用smallInt。
Oracle number(p, s) p+ |s| <= 4
SQL> create table o_test1( value number(2,-2));
表已创建。
SQL> insert into o_test1 values(9950);
insert into o_test1 values(9950)
*
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度
SQL> insert into o_test1 values(9940);
已创建 1 行。
SQL> select * from o_test1;
VALUE
----------
9900
PostgreSQL smallint
postgres=# create table p_test1( value smallint);
CREATE TABLE
postgres=# insert into p_test1 values(9950);
INSERT 0 1
postgres=# insert into p_test1 values(9940);
INSERT 0 1
postgres=# select * from p_test1;
value
-------
9950
9940
(2 行记录)
1.3.2、当4 < p+ |s| <= 9时，使用Integer
当4 < p+ |s| <= 9时，应该使用Integer。
Oracle number(p, s) 4 < p+ |s| <= 9
SQL> create table o_test1( value number(6,-3));
表已创建。
SQL> insert into o_test1 values(999999500);
insert into o_test1 values(999999500)
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度
SQL> insert into o_test1 values(999999400);
已创建 1 行。
SQL> select * from o_test1;
VALUE
----------
999999000
PostgreSQL integer
postgres=# create table p_test1( value integer);
CREATE TABLE
postgres=# insert into p_test1 values(999999500);
INSERT 0 1
postgres=# insert into p_test1 values(999999400);
INSERT 0 1
postgres=# select * from p_test1;
value
-----------
999999500
999999400
(2 行记录)
1.3.3、当9 < p+ |s| <= 18时，使用bigint
当9 < p+ |s| <= 18时，这个时候Integer的精度已经不够了，可以使用bigint。
Oracle 9 < p+ |s| <= 18
SQL> create table o_test1( value number(12,-6));
表已创建。
SQL> insert into o_test1 values(999999999999500000);
insert into o_test1 values(999999999999500000)
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度
SQL> insert into o_test1 values(999999999999400000);
已创建 1 行。
PostgreSQL bigint
postgres=# create table p_test1( value bigint);
CREATE TABLE
postgres=# insert into p_test1 values(999999999999500000);
INSERT 0 1
postgres=# insert into p_test1 values(999999999999400000);
INSERT 0 1
postgres=# select * from p_test1;
value
--------------------
999999999999500000
999999999999400000
(2 行记录)
1.3.4、当p+ |s| >18时，使用numeric(p + |s|, 0)
当p+ |s| >18的时候，只能使用numeric(p + |s|, 0)。
Oracle p+ |s| > 18
SQL> create table o_test1( value number(15,-6));
表已创建。
SQL> insert into o_test1 values(999999999999999500000);
insert into o_test1 values(999999999999999500000)
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度
SQL> insert into o_test1 values(999999999999999400000);
已创建 1 行。
SQL> select * from o_test1;
VALUE
------------------------------
999999999999999000000
PostgreSQL numeric(p + |s|)
postgres=# create table p_test1( value numeric(21));
CREATE TABLE
postgres=# insert into p_test1 values(999999999999999500000);
INSERT 0 1
postgres=# insert into p_test1 values(999999999999999400000);
INSERT 0 1
postgres=# select * from p_test1;
value
-----------------------
999999999999999500000
999999999999999400000
2、非精确类型(浮点数类型)
浮点数是不精确的、变精度的数字类型。由于有下层处理器、操作系统和编译器对它的支持，所以很多情况下处理速度会快的多。但是由于只是以近似值存储的，对于想得到精确值的情况，不可以使用。
Oracle的float类型包括float[(p)]， Binary_float, Binary_double三种类型。基本上和PostgreSQL的real和double precision相对应。PostgreSQL也提供了float(p)类型，但是和real和double precision是基本相同的，所以此处只讨论real和double precision两种。
2.1、Oracle float(p)
Oracle的float类型，根据值的范围区间，可以简单认为在1E-37 ~1E+37之间时，和PostgreSQL的real对应。超出这个区间的时候，则和PostgreSQL的double precision对应。但是精度方面，PostgreSQL尚达不到Oracle的float能达到的精度。
Oracle的 float的精度p是用二进制的1-126表示，若转化为10进制，则需要乘以0.30103。所以，大致相当于1-126*0.30103 ~ 1- 38。而PostgreSQL的real精度是6位，而double precision精度大概是在15位，可以通过修改pg_settings的extra_float_digits参数的值增加-15~3位的精度。但还支持不到更大的位数。但在实际应用中，不会有人把浮点数放到那么大的精度。PostgreSQL默认的精度已经足够。
Oracle float(p)
SQL> create table o_test1( value float(126));
表已创建。
SQL> insert into o_test1 values(12345678901234567890123456789012345678901234567890);
已创建 1 行。
SQL> select * from o_test1;
VALUE
--------------------------------------------------
12345678901234567890123456789012345679000000000000
PostgreSQL double precision
postgres=# create table p_test1(value double precision);
CREATE TABLE
postgres=# insert into p_test1 values(12345678901234567890123456789012345678901234567890);
INSERT 0 1
postgres=# select * from p_test1;
value
-----------------------
1.23456789012346e+049
2.2、Binary_float类型
Binary_float类型，对应于PostgreSQL的real类型。
Binary_float的值在1.17549E-38F 至3.40282E+38F之间，对应于PostgreSQL的real类型。real类型的精度至少是6位小数。可以通过修改pg_settings的extra_float_digits参数的值，调整real的精度和Binary_float相符合。
Oracle Binary_Float
SQL> create table o_test1(col1 BINARY_float);
表已创建。
SQL> insert into o_test1 values (123456789012345678901234567890123456789);
已创建 1 行。
SQL> insert into o_test1 values (1234567890123456789012345678901234567890);
已创建 1 行。
SQL> select * from o_test1;
COL1
--------------------
1.23456786E+038
Inf
PostgreSQL real
postgres=# create table p_test1(value real);
CREATE TABLE
postgres=# insert into p_test1 values(123456789012345678901234567890123456789);
INSERT 0 1
postgres=# insert into p_test1 values(1234567890123456789012345678901234567890);
错误: 值超出范围: 上溢
postgres=# select * from p_test1;
value
--------------
1.23457e+038
2.3、Binary_double类型
Binary_double类型，对应于PostgreSQL的double precision类型。
Binary_double的值在2.22507485850720E-308 至 1.79769313486231E+308之间。对应的PostgreSQL的double precision的范围通常是1E-307到1E+308， 精度至少15位数字。
Oracle Binary_double
SQL> create table o_test1(col1 Binary_double);
表已创建。
SQL> insert into o_test1 values (12345678901234567890123456789012345678901234567890);
已创建 1 行。
SQL> select * from o_test1;
COL1
--------------------
1.2345678901235E+049
PostgreSQL double precision
postgres=# create table p_test1(value double precision);
CREATE TABLE
postgres=# insert into p_test1 values(12345678901234567890123456789012345678901234567890);
INSERT 0 1
postgres=# select * from p_test1;
value
-----------------------
1.23456789012346e+049
(1 行记录)
Oracle的数字类型向PostgreSQL的数据迁移过程中，只要根据Oracle的数据的精度，在PostgreSQL中选择相同或者更大精度的类型。数据就能够迁移过来。但为了转换过来后数据库的效率（特别是整数的时候），需要选择合适的数据类型，才能够完整、正确并且高效的完成Oracle数字类型向PostgreSQL数字类型的迁移。
参考文档：
PostgreSQL 9.4.4 中文手册：数值类型
http://www.postgres.cn/docs/9.4/datatype-numeric.html
Database SQL Language Reference：Data Types
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#i45441
--------------------------------------------------------------
三：日期时间类型
Oracle日期时间类型有两类，一类是日期时间类型，包括Date， Timestamp with time zone， Timestamp with local time zone。另一类是Interval类型。主要有Interval year to month 和Interval day to second两种。PostgreSQL也有类似的两类。其中的日期时间类型包括Timestamp with time zone， Timestamp without time zone， Date，Time with time zone , Time without time zone五种。Interval类型就是Interval。它们之间的对应关系是什么呢?
1、日期时间类型
1.1、Date类型
Oracle的Date类型包括年、月、日、时、分、秒六个字段， 时间跨度是公元前4712年1月1日~公元9999年12月31日。PostgreSQL中，可以使用Timestamp(0) without time zone来对应，时间跨度从公元前4713年~公元294276年。
所有Oracle的日期时间类型，时间跨度都是公元前4712年1月1日~公元9999年12月31日。而PostgreSQL的时间跨度远远超过Oracle的时间跨度。PostgreSQL中Timestamp的时间跨度远大于Oracle的，而Date类型的时间跨度比Timestamp的更大。所以在时间的极值方面不存在问题。
注意：PostgreSQL中的Date类型仅仅包含日期，不包括时、分、秒信息。因此不能使用Date类型来对应Oracle的Date类型。
Oracle Date
SQL> create table o_test(value date);
表已创建。
SQL> insert into o_test values(to_date('-4712-01-01 00:30:45', 'syyyy-mm-dd hh24:mi:ss'));
已创建 1 行。
SQL> insert into o_test values(to_date('-4712-01-01 00:30:45', 'syyyy-mm-dd hh24
:mi:ss') - interval '1' day);
insert into o_test values(to_date('-4712-01-01 00:30:45', 'syyyy-mm-dd hh24:mi:s
s') - interval '1' day)
第 1 行出现错误:
ORA-01841: (完整) 年份值必须介于 -4713 和 +9999 之间, 且不为 0
SQL> insert into o_test values(to_date('9999-12-31 12:30:45', 'yyyy-mm-dd hh24:mi:ss'));
已创建 1 行。
SQL> insert into o_test values(to_date('9999-12-31 12:30:45', 'yyyy-mm-dd hh24:m
i:ss') + interval '1' day);
insert into o_test values(to_date('9999-12-31 12:30:45', 'yyyy-mm-dd hh24:mi:ss'
) + interval '1' day)
第 1 行出现错误:
ORA-01841: (完整) 年份值必须介于 -4713 和 +9999 之间, 且不为 0
SQL> select to_char(value, 'syyyy/mm/dd hh24:mi:ss') from o_test;
TO_CHAR(VALUE,'SYYYY
--------------------
-4712/01/01 00:30:45
9999/12/31 12:30:45
PostgreSQL Timestamp(0) without time zone
postgres=# create table p_test1(value timestamp(0) without time zone);
CREATE TABLE
postgres=# insert into p_test1 values('4712-01-01 00:30:45BC');
INSERT 0 1
postgres=# insert into p_test1 values(to_timestamp('4712-01-01 00:30:45BC','yyyy-mm-dd hh24:mi:ssBC') - interval '1 day');
INSERT 0 1
postgres=# insert into p_test1 values('9999-12-31 23:59:59');
INSERT 0 1
postgres=# insert into p_test1 values(to_timestamp('9999-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss') + interval '1 day');
INSERT 0 1
postgres=# select * from p_test1;
value
------------------------
4712-01-01 00:30:45 BC
4713-12-31 00:30:45 BC
9999-12-31 23:59:59
10000-01-01 23:59:59
(4 行记录)
1.2、Timestamp(p)类型
Oracle的Timestamp包含年、月、日、时、分、秒、毫秒。其中最后的毫秒的精度最大为9位，默认为6位。基本上等同于PostgresQL的Timestamp without time zone。后者同样包含年、月、日、时、分、秒、毫秒。最后的毫秒精度最大为6位，默认为6位。精度方面，PostgreSQL比Oracle稍差。但是实际应用中，毫秒的6位精度已经足够。
Oracle数据中的毫秒数如果小于精度，会在有效数据后自动以0补足位数。PostgreSQL的毫秒数如果小于精度，不会在末尾补0。
Oracle Timestamp 默认精度
SQL> create table o_test(value timestamp);
表已创建。
SQL> insert into o_test values(to_timestamp('2012-12-31 12:30:50.123456', 'yyyy-mm-dd hh24:mi:ss.ff6'));
已创建 1 行。
SQL> insert into o_test values(to_timestamp('2012-12-31 12:30:50.123456789', 'yyyy-mm-dd hh24:mi:ss.ff9'));
已创建 1 行。
SQL> insert into o_test values(to_timestamp('2012-12-31 12:30:50.123', 'yyyy-mm-dd hh24:mi:ss.ff'));
已创建 1 行。
SQL> select * from o_test;
VALUE
---------------------------------------------------------------------------
31-12月-12 12.30.50.123456 下午
31-12月-12 12.30.50.123457 下午
31-12月-12 12.30.50.123000 下午
PostgreSQL Timestamp without time zone 默认精度
postgres=# create table p_test1(value timestamp without time zone);
CREATE TABLE
postgres=# insert into p_test1 values('2012-12-31 12:30:45.123456');
INSERT 0 1
postgres=# insert into p_test1 values('2012-12-31 12:30:45.123456789');
INSERT 0 1
postgres=# insert into p_test1 values('2012-12-31 12:30:45.123');
INSERT 0 1
postgres=# select * from p_test1;
value
----------------------------
2012-12-31 12:30:45.123456
2012-12-31 12:30:45.123457
2012-12-31 12:30:45.123
(3 行记录)
Oracle Timestamp 指定精度
SQL> create table o_test1(value timestamp(9));
表已创建。
SQL> insert into o_test1 values(to_timestamp('2012-12-31 12:30:50.123456789', 'yyyy-mm-dd hh24:mi:ss.ff9'));
已创建 1 行。
SQL> insert into o_test1 values(to_timestamp('2012-12-31 12:30:50.123', 'yyyy-mm-dd hh24:mi:ss.ff'));
已创建 1 行。
SQL> select * from o_test1;
VALUE
---------------------------------------------------------------------------
31-12月-12 12.30.50.123456789 下午
31-12月-12 12.30.50.123000000 下午
PostgreSQL Timestamp without time zone 指定精度
postgres=# create table p_test2(value timestamp(3) without time zone);
CREATE TABLE
postgres=# insert into p_test2 values('2012-12-31 12:30:45.123456789');
INSERT 0 1
postgres=# insert into p_test2 values('2012-12-31 12:30:45.123');
INSERT 0 1
postgres=# insert into p_test2 values('2012-12-31 12:30:45.1');
INSERT 0 1
postgres=# select * from p_test2;
value
-------------------------
2012-12-31 12:30:45.123
2012-12-31 12:30:45.123
2012-12-31 12:30:45.1
(3 行记录)
1.3、Timestamp(p) with time zone类型
Oracle的Timestamp(p) with time zone类型，是在数据库的字段里面保存本字段相关的时区信息。功能方面基本上等同于PostgreSQL的Timestamp(p) with time zone。不同之处在于：Oracle会在数据库字段中存储数据所在的时区，而PostgreSQL却是把数据的时区自动转换成为数据库的时区(初始值由Postgresql.conf中的timezone定义，可以使用set timezone来进行更改)。
数据精度方面，和 1.2、Timestamp(p)类型 相同。
Oracle Timestamp with time zone
SQL> create table o_test( value timestamp with time zone);
表已创建。
SQL> insert into o_test values(systimestamp);
已创建 1 行。
SQL> insert into o_test values(to_timestamp_tz('2012-12-31 12:30:50.123456 +10:0
0', 'yyyy-mm-dd hh24:mi:ss.ff tzh:tzm'));
已创建 1 行。
SQL> select * from o_test;
VALUE
---------------------------------------------------------------------------
30-6月 -16 02.16.47.836000 下午 +08:00
31-12月-12 12.30.50.123456 下午 +10:00
PostgreSQL Timestamp(p) with time zone
postgres=# create table p_test1(value timestamp with time zone);
CREATE TABLE
postgres=# insert into p_test1 values(current_timestamp);
INSERT 0 1
postgres=# insert into p_test1 values('2012-12-31 12:30:50.123456+10');
INSERT 0 1
postgres=# insert into p_test1 values('2012-12-31 12:30:50.123456 PST');
INSERT 0 1
postgres=# select * from p_test1;
value
-------------------------------
2016-06-30 15:35:18.345153+08
2012-12-31 10:30:50.123456+08
2013-01-01 04:30:50.123456+08
(3 行记录)
1.4、Timestamp(p) with local time zone类型
Oracle的Timestamp(p) with local time zone类型，是在数据库的字段中不保存本字段的时区信息。而是使用数据库的time zone信息。当将数据写入数据库的时候，会自动将数据转换为数据库的时区写入。当取得数据的时候，会将数据转化为用户Session所在的时区进行显示。这种数据类型也对应于PostgreSQL的Timestamp with time zone。
Oracle Tiimestamp with local time zone
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
+08:00
SQL> create table o_test( value timestamp with local time zone);
表已创建。
SQL> insert into o_test values(systimestamp);
已创建 1 行。
SQL> insert into o_test values(to_timestamp_tz('2012-12-31 12:30:50.123456 +10:00', 'yyyy-mm-dd hh24:mi:ss:ff tzh:tzm'));
已创建 1 行。
SQL> select * from o_test;
VALUE
---------------------------------------------------------------------------
01-7月 -16 09.49.20.860000 上午
31-12月-12 10.30.50.123456 上午
SQL> alter session set TIME_ZONE='-5:00';
会话已更改。
SQL> select * from o_test;
VALUE
---------------------------------------------------------------------------
30-6月 -16 08.49.20.860000 下午
30-12月-12 09.30.50.123456 下午
PostgreSQL Timestamp(p) with time zone
postgres=# show timezone;
TimeZone
----------------
Asia/Hong_Kong
(1 行记录)
postgres=# create table p_test1(value timestamp with time zone);
CREATE TABLE
postgres=# insert into p_test1 values(current_timestamp);
INSERT 0 1
postgres=# insert into p_test1 values('2012-12-31 12:30:50.123456 +10:00');
INSERT 0 1
postgres=# select * from p_test1;
value
-------------------------------
2016-07-01 10:09:05.958953+08
2012-12-31 10:30:50.123456+08
(2 行记录)
postgres=# set timezone to '-5';
SET
postgres=# select * from p_test1;
value
-------------------------------
2016-06-30 21:09:05.958953-05
2012-12-30 21:30:50.123456-05
(2 行记录)
2、Interval类型
Oracle的Interval 类型表示时间的间隔。分为Interval year(p) to month 和 Interval day(p) to second两类。分别对应间隔为年月和 间隔为日、时、分、秒的情况。PostgreSQL的数据类型中，也有Interval 数据类型。
2.1、Interval year(p) to month类型
Oracle的Interval year(p) to month类型表示年月的时间间隔。year, month一起使用的时候，写法类似于Interval '12-11' year to month。其中的年份根据精度p来决定，p值为1~4，默认为2。月份只能是0~11之间的值。其实year, month都可以单独使用。单独使用时，年份同样是根据精度p来决定。PostgreSQL的Interval中，有类似的用法。而且用法比Oracle还灵活。
Oracle Interval year(p) to month
SQL> create table o_test(value interval year to month);
表已创建。
SQL> insert into o_test values(interval '100-5' year(3) to month);
insert into o_test values(interval '100-5' year(3) to month)
第 1 行出现错误:
ORA-01873: 间隔的前导精度太小
SQL> insert into o_test values(interval '10-5' year to month);
已创建 1 行。
SQL> insert into o_test values(interval '10-12' year to month);
insert into o_test values(interval '10-12' year to month)
第 1 行出现错误:
ORA-01843: 无效的月份
SQL> insert into o_test values(interval '30' year );
已创建 1 行。
SQL> insert into o_test values(interval '1199' month);
已创建 1 行。
SQL> select * from o_test;
VALUE
---------------------------------------------------------------------------
+10-05
+30-00
+99-11
PostgreSQL Interval
postgres=# create table p_test1(value interval);
CREATE TABLE
postgres=# insert into p_test1 values('100-5');
INSERT 0 1
postgres=# insert into p_test1 values('90-12');
错误: 间隔字段超出范围: "90-12"
第1行insert into p_test1 values('90-12');
^
postgres=# insert into p_test1 values('90-11');
INSERT 0 1
postgres=# insert into p_test1 values('90-0');
INSERT 0 1
postgres=# insert into p_test1 values('10000 year');
INSERT 0 1
postgres=# insert into p_test1 values('12000 month');
INSERT 0 1
postgres=# insert into p_test1 values('1000 year 12000 month');
INSERT 0 1
postgres=# select * from p_test1;
value
------------------
100 years 5 mons
90 years 11 mons
90 years
10000 years
1000 years
2000 years
(6 行记录)
2.2、Interval day(p) to second类型
Oracle的Interval day(p) to second类型表示日、时、分、秒的时间间隔。一起使用的时候，写法类似于Interval '12 5:6:7.8' day to second。其中的日期由精度p来决定，p值为0~9，默认为2。小时为0~23，分钟为0~59，秒为0~59。其实日、时、分、秒也都可以单独使用。单独使用时，日期，时、分、秒的值都可以使用精度p来控制。
PostgreSQL的Interval中，也有类似的用法，而且用法比Oracle灵活的多。
Oracle Interval day(p) to second
SQL> create table o_test(value interval day(3) to second);
表已创建。
SQL> insert into o_test values('1000 1:23:45');
insert into o_test values('1000 1:23:45')
第 1 行出现错误:
ORA-01873: 间隔的前导精度太小
SQL> insert into o_test values('999 1:23:45');
已创建 1 行。
SQL> insert into o_test values('100 24:50:45');
insert into o_test values('100 24:50:45')
第 1 行出现错误:
ORA-01850: 小时值必须介于 0 和 23 之间
SQL> insert into o_test values('100 23:60:45');
insert into o_test values('100 23:60:45')
第 1 行出现错误:
ORA-01851: 分钟值必须介于 0 和 59 之间
SQL> insert into o_test values('100 23:40:75');
insert into o_test values('100 23:40:75')
第 1 行出现错误:
ORA-01852: 秒值必须介于 0 和 59 之间
SQL> insert into o_test values(interval '2399' hour);
已创建 1 行。
SQL> insert into o_test values(interval '2400' hour);
insert into o_test values(interval '2400' hour)
第 1 行出现错误:
ORA-01873: 间隔的前导精度太小
SQL> insert into o_test values(interval '2400' hour(4));
已创建 1 行。
SQL> insert into o_test values(interval '800000' minute);
insert into o_test values(interval '800000' minute)
第 1 行出现错误:
ORA-01873: 间隔的前导精度太小
SQL> insert into o_test values(interval '800000' minute(6));
已创建 1 行。
SQL> insert into o_test values(interval '80000000' second);
insert into o_test values(interval '80000000' second)
第 1 行出现错误:
ORA-01873: 间隔的前导精度太小
SQL> insert into o_test values(interval '80000000' second(8));
已创建 1 行。
SQL> select * from o_test;
VALUE
---------------------------------------------------------------------------
+999 01:23:45.000000
+099 23:00:00.000000
+100 00:00:00.000000
+555 13:20:00.000000
+925 22:13:20.000000
PostgreSQL Interval
postgres=# create table p_test1(value interval);
CREATE TABLE
postgres=# insert into p_test1 values('1000 1:23:45');
INSERT 0 1
postgres=# insert into p_test1 values('100 50:23:45');
INSERT 0 1
postgres=# insert into p_test1 values('100 50:60:45');
错误: 间隔字段超出范围: "100 50:60:45"
第1行insert into p_test1 values('100 50:60:45');
^
postgres=# insert into p_test1 values('1000 day');
INSERT 0 1
postgres=# insert into p_test1 values('80000 hour');
INSERT 0 1
postgres=# insert into p_test1 values('8000000 minute');
INSERT 0 1
postgres=# insert into p_test1 values('800000000 second');
INSERT 0 1
postgres=# select * from p_test1;
value
--------------------
1000 days 01:23:45
100 days 50:23:45
1000 days
80000:00:00
133333:20:00
222222:13:20
(6 行记录)
Oracle的日期时间类型向PostgreSQL的数据迁移过程相对来说简单一些。由于PostgreSQL的数据类型的极值超越Oracle，因此，数据迁移过程中，只要根据Oracle的数据精度，在PostgreSQL中选择正确的数据类型。并留意一下二者SQL写法的不同，应该就能够完整正确的迁移过来。
参考文档：
PostgreSQL 9.4.4 中文手册：日期/时间类型
http://www.postgres.cn/docs/9.4/datatype-datetime.html
Database SQL Language Reference：Data Types
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#i45441
--------------------------------------------------------------
四：大数据类型
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类型。这是一种特殊的数据类型。数据并不保存在数据库内。数据库内仅仅是一个文件的指针。指明了文件的路径和文件名。实际的文件存储在硬盘上。只能对文件进行读取，而且不能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
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
--------------------------------------------------------------
五：其它类型
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
--------------------------------------------------------------
一：字符类型
二：数字类型
三：日期时间类型
四：大数据类型
五：其它类型
本文总结息《卸甲笔记》，感谢原作者。
--------------------------------------------------------------
一：字符类型
PostgreSQL是世界上功能最强大的开源数据库，在国内得到了越来越多机构和开发者的青睐和应用。随着PostgreSQL的应用越来越广泛，Oracle向PostgreSQL数据库的数据迁移需求也越来越多。数据库之间数据迁移的时候，首先遇到的，并且也是最重要的，就是数据类型之间的转换。下面根据自己的理解和测试，写了一些数据类型之间的差异以及迁移时的注意事项的文章，不足之处，尚请多多指教。
字符类型
大家知道，Oracle有四种字符类型，分别是char，varchar2，nchar，nvarchar2。而PostgreSQL只有两种字符类型，分别是character (char)和 character varying(varchar)。它们之间的有什么异同呢？
1、数据最小单位
Oracle：char和varchar2的数据最小单位，默认值为BYTE，即字节数。可以修改为CHAR，则为字符。通过参数NLS_LENGTH_SEMANTICS来控制。
而Oracle的nchar和nvarchar2是针对特定字符集来的，数据最小单位根据字符集不同而不同。如字符集是AL16UTF16，就是2个字节，而字符集是UTF8，就是3个，通过参数NLS_NCHAR_CHARACTERSET来控制（我的Oracle的NLS_NCHAR_CHARACTERSET值就是AL16UTF16，一个字为2byte）。
当数据不够最大长度的时候，char和nchar写入/取出的是最大长度的字符串，超出实际长度的使用空格补齐。而varchar2和 nvarchar2写入/取出的是实际操作的字符串。
SQL> create table o_test(id int primary key,
2 name char(5),
3 n_name nchar(5),
4 addr varchar2(5),
5 n_addr nvarchar2(5));
表已创建。
SQL> insert into o_test values(1, 'li', 'li', 'BJ', 'BJ');
已创建 1 行。
SQL> select lengthb(name), lengthb(n_name), lengthb(addr),lengthb(n_addr)
2 from o_test;
LENGTHB(NAME) LENGTHB(N_NAME) LENGTHB(ADDR) LENGTHB(N_ADDR)
------------- --------------- ------------- ---------------
5 10 2 4
PostgreSQL：char和 varchar的数据最小单位都是字符(CHAR)。
当数据不够最大长度的时候，char类型的数据物理上用空白填充到指定的长度，并且以这种方式存储和显示。但是，填充的空白是无意义的。在取出来实际使用的时候，空白会自动被删除。VARCHAR类型的数据，会按照实际长度存储。最后的空白都是有意义的，不会被删除。
highgo=# create table p_test(id smallint primary key,
highgo(# name char(5),
highgo(# v_name varchar(5));
CREATE TABLE
highgo=# insert into p_test values(1,'赵 ', '赵老大 ');
INSERT 0 1
highgo=# select length(name), length(v_name) from p_test;
length | length
--------+--------
1 | 4
(1 行记录)
2、数据类型的最大长度
Oracle：char类型的最大长度是2000字节，varchar2是4000个字节。
nchar，nvarchar2类型的最大长度根据数据集不同而不同。最终的byte数不能超过2000和4000。如字符集为AL16UTF16，则nchar的为2000/2=1000，而nvarchar2则为4000/2=2000
SQL> create table o_test2(name char(2001));
create table o_test2(name char(2001))
第 1 行出现错误:
ORA-00910: 指定的长度对于数据类型而言过长
SQL> create table o_test2(name char(2000));
表已创建。
SQL> create table o_test3(name varchar2(4001));
create table o_test3(name varchar2(4001))
第 1 行出现错误:
ORA-00910: 指定的长度对于数据类型而言过长
SQL> create table o_test3(name varchar2(4000));
表已创建。
SQL> create table o_table5(name nchar(1001));
create table o_table5(name nchar(1001))
第 1 行出现错误:
ORA-00910: 指定的长度对于数据类型而言过长
SQL> create table o_table5(name nchar(1000));
表已创建。
SQL> create table o_table6(name nvarchar2(2001));
create table o_table6(name nvarchar2(2001))
第 1 行出现错误:
ORA-00910: 指定的长度对于数据类型而言过长
SQL> create table o_table6(name nvarchar2(2000));
表已创建。
PostgreSQL：理论最大数据长度是1G，默认值是10 1024 1024=10485760（具体的值由htup_details.h的MaxAttrSize决定）
highgo=# create table p_test3(name char(10485761));
错误: 类型 char 的长度不能超过 10485760
第1行create table p_test3(name char(10485761));
^
highgo=# create table p_test3(name char(10485760));
CREATE TABLE
highgo=# create table p_test4(name varchar(10485761));
错误: 类型 varchar 的长度不能超过 10485760
第1行create table p_test4(name varchar(10485761));
^
highgo=# create table p_test4(name varchar(10485760));
CREATE TABLE
3、定义数据时的参数（字符数)
Oracle：char类型，不带字符数的时候，是1个字符（char(1)），而varchar2必须有字符数。
nchar和nvarchar2分别与char，varchar2类似。
SQL> create table o_test1(name char, addr varchar);
create table o_test1(name char, addr varchar)
第 1 行出现错误:
ORA-00906: 缺失左括号
SQL> create table o_test1(name char, addr varchar(5));
表已创建。
SQL> insert into o_test1 values('12','OK');
insert into o_test1 values('12','OK')
第 1 行出现错误:
ORA-12899: 列 "SCOTT"."O_TEST1"."NAME" 的值太大 (实际值: 2, 最大值: 1)
SQL> insert into o_test1 values('1','OK');
已创建 1 行。
PostgreSQL：char类型，不带字符数的时候，也是默认一个字符(char(1))，而varchar不带字符数的时候，没有限制。
highgo=# create table p_test1(name char, addr varchar);
CREATE TABLE
highgo=# insert into p_test1 values('12', '1234567890');
错误: 对于字符类型来说这个值太长了(1)
highgo=# insert into p_test1 values('1', '1234567890');
INSERT 0 1
4、插入数据库的时候的，字符数超出最大长度部分的处理。
Oracle：报错。即使超出的部分是字符末的空格。
SQL> insert into o_test1 values('1','123456');
insert into o_test1 values('1','123456')
第 1 行出现错误:
ORA-12899: 列 "SCOTT"."O_TEST1"."ADDR" 的值太大 (实际值: 6, 最大值: 5)
SQL> insert into o_test1 values('1','1234 ');
insert into o_test1 values('1','1234 ')
第 1 行出现错误:
ORA-12899: 列 "SCOTT"."O_TEST1"."ADDR" 的值太大 (实际值: 6, 最大值: 5)
PostgreSQL：当超出部分是有效的字符的时候，报错。
当是最后的空格时，截断为最大长度，不报错。但是当使用明确的类型转换为最大字符数的时候，截断为最大长度，不报错。
highgo=# create table p_test2(name char(2), addr varchar(5));
CREATE TABLE
highgo=# insert into p_test2 values('123', '12345');
错误:对于字符类型来说这个值太长了(2)
highgo=# insert into p_test2 values('12 ', '12345');
INSERT 0 1
highgo=# insert into p_test2 values(102 :: char(2) , '12345');
INSERT 0 1
highgo=# select * from p_test2;
name | addr
------+-------
12 | 12345
10 | 12345
(2 行记录)
字符类型数据迁移过程中，应该先根据Oracle数据的字符集和数据长度，从类型和长度上保证对方的数据能够完全的转换过来。再注意一些转换过程中SQL的写法，字符型的数据基本上就能够完全的、正确的迁移到PostgreSQL数据库中来了。
参考文档：
PostgreSQL 9.4.4 中文手册：8.3.字符类型
http://www.postgres.cn/docs/9.4/datatype-character.html
Database SQL Language Reference：Oracle Built-in Data Types
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#SQLRF30020
--------------------------------------------------------------
二：数字类型
数字类型
Oracle内建的数字类型有四种，分别是number，float，binary_float，binary_double。由于和其余数据库的数据兼容，而产生了smallint，int，Integer，Decimal等多种数字类型，其实内部都是使用内建的四种实现的。因此我们只讨论内建的四种数据类型。
PostgreSQL的数字类型有三类。分别是 整数类型，包括smallint，integer和bigint。任意精度类型，包括numeric和decimal两种(其中decimal等同于numeric)，以及浮点数类型，包括real和double。它们之间的区别和迁移时应该注意什么呢？下面按照精确类型(整数类型和任意精度类型)和非精确类型(浮点数类型)两类来进行讨论。
1、精确类型(整数和任意精度数值)
Oracle的number(p, s) 类型，根据精度(p)和小数位数(s)的不同，分别对应PostgreSQL中的多种情况。
1.1、当小数位数(s)=0时
当小数位数(s)=0时，可以对应PostgreSQL的smallint, integer, bigint, numeric(p)等多种数据类型。由于smallint，Integer，bigint的算术运算效率比numberic高的多，所以迁移时建议如下处理：
1.1.1、当p <= 4时，使用integer或者smallint
当p <= 4时，可以使用smallint和Integer，推荐使用Integer，因为它在取值范围、存储空间、性能之间最为平衡。只有磁盘空间紧张的情况下，才建议使用smallint。
Oracle number(p, 0) p <=4
SQL> create table o_test1( value number(4,0));
表已创建。
SQL> insert into o_test1 values(99999);
insert into o_test1 values(99999)
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度
SQL> insert into o_test1 values(9999);
已创建 1 行。
PostgreSQL smallint
postgres=# create table p_test1(value smallint);
CREATE TABLE
postgres=# insert into p_test1 values(99999);
错误: smallint 超出范围
postgres=# insert into p_test1 values(9999);
INSERT 0 1
1.1.2、当4 < p <= 9时，应该使用integer
Oracle number(p, 0) 4 < p <= 9
SQL> create table o_test1(value number(9,0));
表已创建。
SQL> insert into o_test1 values(9999999999);
insert into o_test1 values(9999999999)
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度
SQL> insert into o_test1 values(999999999);
已创建 1 行。
PostgreSQL integer
postgres=# create table p_test1(value integer);
CREATE TABLE
postgres=# insert into p_test1 values(9999999999);
错误: 整数超出范围
postgres=# insert into p_test1 values(999999999);
INSERT 0 1
1.1.3、当9 < p <= 18时，使用bigint
当9 < p <= 18时，这个时候integer的精度已经不够了，应该使用bigint
Oracle number(p, 0) 9 < p <= 18
SQL> create table o_test1(value number(18,0));
表已创建。
SQL> insert into o_test1 values(9999999999999999999);
insert into o_test1 values(9999999999999999999)
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度
SQL> insert into o_test1 values(999999999999999999);
已创建 1 行。
PostgreSQL bigint
postgres=# create table p_test1 (value bigint);
CREATE TABLE
postgres=# insert into p_test1 values(9999999999999999999);
错误: bigint 超出范围
postgres=# insert into p_test1 values(999999999999999999);
INSERT 0 1
1.1.4、当p > 18时，使用numeric(p, 0)
当p>18的时候，需要使用numeric(p, 0)。才能保证数据转换的精度。
Oracle number(p, 0) p > 18
SQL> create table o_test1(value number(19,0));
表已创建。
SQL> insert into o_test1 values(9999999999999999999);
已创建 1 行。
PostgreSQL numeric(19, 0)
postgres=# create table p_test1 (value numeric(19, 0));
CREATE TABLE
postgres=# insert into p_test1 values(9999999999999999999);
INSERT 0 1
1.2、当小数位数(s) > 0时
当小数位数(s) > 0时，由于有小数，只能够使用numeric(p, s)。
1.2.1、当0 < s <=p的时候，使用numeric(p, s)
Oracle number(p, s) 0 < s <= p
SQL> create table o_test1(value number(5,3));
表已创建。
SQL> insert into o_test1 values(123.567);
insert into o_test1 values(123.567)
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度
SQL> insert into o_test1 values(12.56789);
已创建 1 行。
SQL> select * from o_test1;
VALUE
----------
12.568
PostgreSQL numeric(p, s) p >= s
postgres=# create table p_test1( value numeric(5, 3));
CREATE TABLE
postgres=# insert into p_test1 values(123.456);
错误: 数字字段溢出
描述: 精度为5,范围是3的字段必须四舍五入到小于10^2的绝对值.
postgres=# insert into p_test1 values(12.456789);
INSERT 0 1
postgres=# select * from p_test1;
value
--------
12.457
(1 行记录)
1.2.2、当 p < s的时候，使用numeric(s, s)
当 p < s的时候，使用numeric(s, s)。此时，Oracle可以控制小数点后0的个数，而PostgreSQL不可。由于数据迁移，只需要把数据按照原来的样式迁移过来即可，所以不存在问题。
Oracle number(p, s) s > p
SQL> create table o_test1(value number(3,5));
表已创建。
SQL> insert into o_test1 values(0.056789);
insert into o_test1 values(0.056789)
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度
SQL> insert into o_test1 values(0.0056789);
已创建 1 行。
SQL> select * from o_test1;
VALUE
----------
.00568
PostgreSQL numeric(p, s) s > p
postgres=# create table p_test1( value numeric(5, 5));
CREATE TABLE
postgres=# insert into p_test1 values(0.056789);
INSERT 0 1
postgres=# insert into p_test1 values(0.0056789);
INSERT 0 1
postgres=# select * from p_test1;
value
---------
0.05679
0.00568
(2 行记录)
1.3、当小数位数(s) < 0时
当小数位数(s) < 0的时候，由于此时p代表精度的位数，s位决定了在何处进行位数的舍入。s < 0的时候，这种根据s来进行数据舍入的功能PostgreSQL尚不具备，但由于数据迁移时只要能将Oracle的数据完全转换过来就行了。所以对于数据迁移来说也不存在问题。
PostgreSQL中，小数位数必须大于等于0。此时的迁移，需要把p + |s| 作为数据的精度来判断。其余和s=0的时候相同。
1.3.1、当p + |s| <= 4时，使用integer或者smallint
当p+ |s| <= 4时，可以使用smallint和Integer，推荐使用Integer，只有磁盘空间紧张的情况下，才建议使用smallInt。
Oracle number(p, s) p+ |s| <= 4
SQL> create table o_test1( value number(2,-2));
表已创建。
SQL> insert into o_test1 values(9950);
insert into o_test1 values(9950)
*
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度
SQL> insert into o_test1 values(9940);
已创建 1 行。
SQL> select * from o_test1;
VALUE
----------
9900
PostgreSQL smallint
postgres=# create table p_test1( value smallint);
CREATE TABLE
postgres=# insert into p_test1 values(9950);
INSERT 0 1
postgres=# insert into p_test1 values(9940);
INSERT 0 1
postgres=# select * from p_test1;
value
-------
9950
9940
(2 行记录)
1.3.2、当4 < p+ |s| <= 9时，使用Integer
当4 < p+ |s| <= 9时，应该使用Integer。
Oracle number(p, s) 4 < p+ |s| <= 9
SQL> create table o_test1( value number(6,-3));
表已创建。
SQL> insert into o_test1 values(999999500);
insert into o_test1 values(999999500)
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度
SQL> insert into o_test1 values(999999400);
已创建 1 行。
SQL> select * from o_test1;
VALUE
----------
999999000
PostgreSQL integer
postgres=# create table p_test1( value integer);
CREATE TABLE
postgres=# insert into p_test1 values(999999500);
INSERT 0 1
postgres=# insert into p_test1 values(999999400);
INSERT 0 1
postgres=# select * from p_test1;
value
-----------
999999500
999999400
(2 行记录)
1.3.3、当9 < p+ |s| <= 18时，使用bigint
当9 < p+ |s| <= 18时，这个时候Integer的精度已经不够了，可以使用bigint。
Oracle 9 < p+ |s| <= 18
SQL> create table o_test1( value number(12,-6));
表已创建。
SQL> insert into o_test1 values(999999999999500000);
insert into o_test1 values(999999999999500000)
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度
SQL> insert into o_test1 values(999999999999400000);
已创建 1 行。
PostgreSQL bigint
postgres=# create table p_test1( value bigint);
CREATE TABLE
postgres=# insert into p_test1 values(999999999999500000);
INSERT 0 1
postgres=# insert into p_test1 values(999999999999400000);
INSERT 0 1
postgres=# select * from p_test1;
value
--------------------
999999999999500000
999999999999400000
(2 行记录)
1.3.4、当p+ |s| >18时，使用numeric(p + |s|, 0)
当p+ |s| >18的时候，只能使用numeric(p + |s|, 0)。
Oracle p+ |s| > 18
SQL> create table o_test1( value number(15,-6));
表已创建。
SQL> insert into o_test1 values(999999999999999500000);
insert into o_test1 values(999999999999999500000)
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度
SQL> insert into o_test1 values(999999999999999400000);
已创建 1 行。
SQL> select * from o_test1;
VALUE
------------------------------
999999999999999000000
PostgreSQL numeric(p + |s|)
postgres=# create table p_test1( value numeric(21));
CREATE TABLE
postgres=# insert into p_test1 values(999999999999999500000);
INSERT 0 1
postgres=# insert into p_test1 values(999999999999999400000);
INSERT 0 1
postgres=# select * from p_test1;
value
-----------------------
999999999999999500000
999999999999999400000
2、非精确类型(浮点数类型)
浮点数是不精确的、变精度的数字类型。由于有下层处理器、操作系统和编译器对它的支持，所以很多情况下处理速度会快的多。但是由于只是以近似值存储的，对于想得到精确值的情况，不可以使用。
Oracle的float类型包括float[(p)]， Binary_float, Binary_double三种类型。基本上和PostgreSQL的real和double precision相对应。PostgreSQL也提供了float(p)类型，但是和real和double precision是基本相同的，所以此处只讨论real和double precision两种。
2.1、Oracle float(p)
Oracle的float类型，根据值的范围区间，可以简单认为在1E-37 ~1E+37之间时，和PostgreSQL的real对应。超出这个区间的时候，则和PostgreSQL的double precision对应。但是精度方面，PostgreSQL尚达不到Oracle的float能达到的精度。
Oracle的 float的精度p是用二进制的1-126表示，若转化为10进制，则需要乘以0.30103。所以，大致相当于1-126*0.30103 ~ 1- 38。而PostgreSQL的real精度是6位，而double precision精度大概是在15位，可以通过修改pg_settings的extra_float_digits参数的值增加-15~3位的精度。但还支持不到更大的位数。但在实际应用中，不会有人把浮点数放到那么大的精度。PostgreSQL默认的精度已经足够。
Oracle float(p)
SQL> create table o_test1( value float(126));
表已创建。
SQL> insert into o_test1 values(12345678901234567890123456789012345678901234567890);
已创建 1 行。
SQL> select * from o_test1;
VALUE
--------------------------------------------------
12345678901234567890123456789012345679000000000000
PostgreSQL double precision
postgres=# create table p_test1(value double precision);
CREATE TABLE
postgres=# insert into p_test1 values(12345678901234567890123456789012345678901234567890);
INSERT 0 1
postgres=# select * from p_test1;
value
-----------------------
1.23456789012346e+049
2.2、Binary_float类型
Binary_float类型，对应于PostgreSQL的real类型。
Binary_float的值在1.17549E-38F 至3.40282E+38F之间，对应于PostgreSQL的real类型。real类型的精度至少是6位小数。可以通过修改pg_settings的extra_float_digits参数的值，调整real的精度和Binary_float相符合。
Oracle Binary_Float
SQL> create table o_test1(col1 BINARY_float);
表已创建。
SQL> insert into o_test1 values (123456789012345678901234567890123456789);
已创建 1 行。
SQL> insert into o_test1 values (1234567890123456789012345678901234567890);
已创建 1 行。
SQL> select * from o_test1;
COL1
--------------------
1.23456786E+038
Inf
PostgreSQL real
postgres=# create table p_test1(value real);
CREATE TABLE
postgres=# insert into p_test1 values(123456789012345678901234567890123456789);
INSERT 0 1
postgres=# insert into p_test1 values(1234567890123456789012345678901234567890);
错误: 值超出范围: 上溢
postgres=# select * from p_test1;
value
--------------
1.23457e+038
2.3、Binary_double类型
Binary_double类型，对应于PostgreSQL的double precision类型。
Binary_double的值在2.22507485850720E-308 至 1.79769313486231E+308之间。对应的PostgreSQL的double precision的范围通常是1E-307到1E+308， 精度至少15位数字。
Oracle Binary_double
SQL> create table o_test1(col1 Binary_double);
表已创建。
SQL> insert into o_test1 values (12345678901234567890123456789012345678901234567890);
已创建 1 行。
SQL> select * from o_test1;
COL1
--------------------
1.2345678901235E+049
PostgreSQL double precision
postgres=# create table p_test1(value double precision);
CREATE TABLE
postgres=# insert into p_test1 values(12345678901234567890123456789012345678901234567890);
INSERT 0 1
postgres=# select * from p_test1;
value
-----------------------
1.23456789012346e+049
(1 行记录)
Oracle的数字类型向PostgreSQL的数据迁移过程中，只要根据Oracle的数据的精度，在PostgreSQL中选择相同或者更大精度的类型。数据就能够迁移过来。但为了转换过来后数据库的效率（特别是整数的时候），需要选择合适的数据类型，才能够完整、正确并且高效的完成Oracle数字类型向PostgreSQL数字类型的迁移。
参考文档：
PostgreSQL 9.4.4 中文手册：数值类型
http://www.postgres.cn/docs/9.4/datatype-numeric.html
Database SQL Language Reference：Data Types
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#i45441
--------------------------------------------------------------
三：日期时间类型
Oracle日期时间类型有两类，一类是日期时间类型，包括Date， Timestamp with time zone， Timestamp with local time zone。另一类是Interval类型。主要有Interval year to month 和Interval day to second两种。PostgreSQL也有类似的两类。其中的日期时间类型包括Timestamp with time zone， Timestamp without time zone， Date，Time with time zone , Time without time zone五种。Interval类型就是Interval。它们之间的对应关系是什么呢?
1、日期时间类型
1.1、Date类型
Oracle的Date类型包括年、月、日、时、分、秒六个字段， 时间跨度是公元前4712年1月1日~公元9999年12月31日。PostgreSQL中，可以使用Timestamp(0) without time zone来对应，时间跨度从公元前4713年~公元294276年。
所有Oracle的日期时间类型，时间跨度都是公元前4712年1月1日~公元9999年12月31日。而PostgreSQL的时间跨度远远超过Oracle的时间跨度。PostgreSQL中Timestamp的时间跨度远大于Oracle的，而Date类型的时间跨度比Timestamp的更大。所以在时间的极值方面不存在问题。
注意：PostgreSQL中的Date类型仅仅包含日期，不包括时、分、秒信息。因此不能使用Date类型来对应Oracle的Date类型。
Oracle Date
SQL> create table o_test(value date);
表已创建。
SQL> insert into o_test values(to_date('-4712-01-01 00:30:45', 'syyyy-mm-dd hh24:mi:ss'));
已创建 1 行。
SQL> insert into o_test values(to_date('-4712-01-01 00:30:45', 'syyyy-mm-dd hh24
:mi:ss') - interval '1' day);
insert into o_test values(to_date('-4712-01-01 00:30:45', 'syyyy-mm-dd hh24:mi:s
s') - interval '1' day)
第 1 行出现错误:
ORA-01841: (完整) 年份值必须介于 -4713 和 +9999 之间, 且不为 0
SQL> insert into o_test values(to_date('9999-12-31 12:30:45', 'yyyy-mm-dd hh24:mi:ss'));
已创建 1 行。
SQL> insert into o_test values(to_date('9999-12-31 12:30:45', 'yyyy-mm-dd hh24:m
i:ss') + interval '1' day);
insert into o_test values(to_date('9999-12-31 12:30:45', 'yyyy-mm-dd hh24:mi:ss'
) + interval '1' day)
第 1 行出现错误:
ORA-01841: (完整) 年份值必须介于 -4713 和 +9999 之间, 且不为 0
SQL> select to_char(value, 'syyyy/mm/dd hh24:mi:ss') from o_test;
TO_CHAR(VALUE,'SYYYY
--------------------
-4712/01/01 00:30:45
9999/12/31 12:30:45
PostgreSQL Timestamp(0) without time zone
postgres=# create table p_test1(value timestamp(0) without time zone);
CREATE TABLE
postgres=# insert into p_test1 values('4712-01-01 00:30:45BC');
INSERT 0 1
postgres=# insert into p_test1 values(to_timestamp('4712-01-01 00:30:45BC','yyyy-mm-dd hh24:mi:ssBC') - interval '1 day');
INSERT 0 1
postgres=# insert into p_test1 values('9999-12-31 23:59:59');
INSERT 0 1
postgres=# insert into p_test1 values(to_timestamp('9999-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss') + interval '1 day');
INSERT 0 1
postgres=# select * from p_test1;
value
------------------------
4712-01-01 00:30:45 BC
4713-12-31 00:30:45 BC
9999-12-31 23:59:59
10000-01-01 23:59:59
(4 行记录)
1.2、Timestamp(p)类型
Oracle的Timestamp包含年、月、日、时、分、秒、毫秒。其中最后的毫秒的精度最大为9位，默认为6位。基本上等同于PostgresQL的Timestamp without time zone。后者同样包含年、月、日、时、分、秒、毫秒。最后的毫秒精度最大为6位，默认为6位。精度方面，PostgreSQL比Oracle稍差。但是实际应用中，毫秒的6位精度已经足够。
Oracle数据中的毫秒数如果小于精度，会在有效数据后自动以0补足位数。PostgreSQL的毫秒数如果小于精度，不会在末尾补0。
Oracle Timestamp 默认精度
SQL> create table o_test(value timestamp);
表已创建。
SQL> insert into o_test values(to_timestamp('2012-12-31 12:30:50.123456', 'yyyy-mm-dd hh24:mi:ss.ff6'));
已创建 1 行。
SQL> insert into o_test values(to_timestamp('2012-12-31 12:30:50.123456789', 'yyyy-mm-dd hh24:mi:ss.ff9'));
已创建 1 行。
SQL> insert into o_test values(to_timestamp('2012-12-31 12:30:50.123', 'yyyy-mm-dd hh24:mi:ss.ff'));
已创建 1 行。
SQL> select * from o_test;
VALUE
---------------------------------------------------------------------------
31-12月-12 12.30.50.123456 下午
31-12月-12 12.30.50.123457 下午
31-12月-12 12.30.50.123000 下午
PostgreSQL Timestamp without time zone 默认精度
postgres=# create table p_test1(value timestamp without time zone);
CREATE TABLE
postgres=# insert into p_test1 values('2012-12-31 12:30:45.123456');
INSERT 0 1
postgres=# insert into p_test1 values('2012-12-31 12:30:45.123456789');
INSERT 0 1
postgres=# insert into p_test1 values('2012-12-31 12:30:45.123');
INSERT 0 1
postgres=# select * from p_test1;
value
----------------------------
2012-12-31 12:30:45.123456
2012-12-31 12:30:45.123457
2012-12-31 12:30:45.123
(3 行记录)
Oracle Timestamp 指定精度
SQL> create table o_test1(value timestamp(9));
表已创建。
SQL> insert into o_test1 values(to_timestamp('2012-12-31 12:30:50.123456789', 'yyyy-mm-dd hh24:mi:ss.ff9'));
已创建 1 行。
SQL> insert into o_test1 values(to_timestamp('2012-12-31 12:30:50.123', 'yyyy-mm-dd hh24:mi:ss.ff'));
已创建 1 行。
SQL> select * from o_test1;
VALUE
---------------------------------------------------------------------------
31-12月-12 12.30.50.123456789 下午
31-12月-12 12.30.50.123000000 下午
PostgreSQL Timestamp without time zone 指定精度
postgres=# create table p_test2(value timestamp(3) without time zone);
CREATE TABLE
postgres=# insert into p_test2 values('2012-12-31 12:30:45.123456789');
INSERT 0 1
postgres=# insert into p_test2 values('2012-12-31 12:30:45.123');
INSERT 0 1
postgres=# insert into p_test2 values('2012-12-31 12:30:45.1');
INSERT 0 1
postgres=# select * from p_test2;
value
-------------------------
2012-12-31 12:30:45.123
2012-12-31 12:30:45.123
2012-12-31 12:30:45.1
(3 行记录)
1.3、Timestamp(p) with time zone类型
Oracle的Timestamp(p) with time zone类型，是在数据库的字段里面保存本字段相关的时区信息。功能方面基本上等同于PostgreSQL的Timestamp(p) with time zone。不同之处在于：Oracle会在数据库字段中存储数据所在的时区，而PostgreSQL却是把数据的时区自动转换成为数据库的时区(初始值由Postgresql.conf中的timezone定义，可以使用set timezone来进行更改)。
数据精度方面，和 1.2、Timestamp(p)类型 相同。
Oracle Timestamp with time zone
SQL> create table o_test( value timestamp with time zone);
表已创建。
SQL> insert into o_test values(systimestamp);
已创建 1 行。
SQL> insert into o_test values(to_timestamp_tz('2012-12-31 12:30:50.123456 +10:0
0', 'yyyy-mm-dd hh24:mi:ss.ff tzh:tzm'));
已创建 1 行。
SQL> select * from o_test;
VALUE
---------------------------------------------------------------------------
30-6月 -16 02.16.47.836000 下午 +08:00
31-12月-12 12.30.50.123456 下午 +10:00
PostgreSQL Timestamp(p) with time zone
postgres=# create table p_test1(value timestamp with time zone);
CREATE TABLE
postgres=# insert into p_test1 values(current_timestamp);
INSERT 0 1
postgres=# insert into p_test1 values('2012-12-31 12:30:50.123456+10');
INSERT 0 1
postgres=# insert into p_test1 values('2012-12-31 12:30:50.123456 PST');
INSERT 0 1
postgres=# select * from p_test1;
value
-------------------------------
2016-06-30 15:35:18.345153+08
2012-12-31 10:30:50.123456+08
2013-01-01 04:30:50.123456+08
(3 行记录)
1.4、Timestamp(p) with local time zone类型
Oracle的Timestamp(p) with local time zone类型，是在数据库的字段中不保存本字段的时区信息。而是使用数据库的time zone信息。当将数据写入数据库的时候，会自动将数据转换为数据库的时区写入。当取得数据的时候，会将数据转化为用户Session所在的时区进行显示。这种数据类型也对应于PostgreSQL的Timestamp with time zone。
Oracle Tiimestamp with local time zone
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
+08:00
SQL> create table o_test( value timestamp with local time zone);
表已创建。
SQL> insert into o_test values(systimestamp);
已创建 1 行。
SQL> insert into o_test values(to_timestamp_tz('2012-12-31 12:30:50.123456 +10:00', 'yyyy-mm-dd hh24:mi:ss:ff tzh:tzm'));
已创建 1 行。
SQL> select * from o_test;
VALUE
---------------------------------------------------------------------------
01-7月 -16 09.49.20.860000 上午
31-12月-12 10.30.50.123456 上午
SQL> alter session set TIME_ZONE='-5:00';
会话已更改。
SQL> select * from o_test;
VALUE
---------------------------------------------------------------------------
30-6月 -16 08.49.20.860000 下午
30-12月-12 09.30.50.123456 下午
PostgreSQL Timestamp(p) with time zone
postgres=# show timezone;
TimeZone
----------------
Asia/Hong_Kong
(1 行记录)
postgres=# create table p_test1(value timestamp with time zone);
CREATE TABLE
postgres=# insert into p_test1 values(current_timestamp);
INSERT 0 1
postgres=# insert into p_test1 values('2012-12-31 12:30:50.123456 +10:00');
INSERT 0 1
postgres=# select * from p_test1;
value
-------------------------------
2016-07-01 10:09:05.958953+08
2012-12-31 10:30:50.123456+08
(2 行记录)
postgres=# set timezone to '-5';
SET
postgres=# select * from p_test1;
value
-------------------------------
2016-06-30 21:09:05.958953-05
2012-12-30 21:30:50.123456-05
(2 行记录)
2、Interval类型
Oracle的Interval 类型表示时间的间隔。分为Interval year(p) to month 和 Interval day(p) to second两类。分别对应间隔为年月和 间隔为日、时、分、秒的情况。PostgreSQL的数据类型中，也有Interval 数据类型。
2.1、Interval year(p) to month类型
Oracle的Interval year(p) to month类型表示年月的时间间隔。year, month一起使用的时候，写法类似于Interval '12-11' year to month。其中的年份根据精度p来决定，p值为1~4，默认为2。月份只能是0~11之间的值。其实year, month都可以单独使用。单独使用时，年份同样是根据精度p来决定。PostgreSQL的Interval中，有类似的用法。而且用法比Oracle还灵活。
Oracle Interval year(p) to month
SQL> create table o_test(value interval year to month);
表已创建。
SQL> insert into o_test values(interval '100-5' year(3) to month);
insert into o_test values(interval '100-5' year(3) to month)
第 1 行出现错误:
ORA-01873: 间隔的前导精度太小
SQL> insert into o_test values(interval '10-5' year to month);
已创建 1 行。
SQL> insert into o_test values(interval '10-12' year to month);
insert into o_test values(interval '10-12' year to month)
第 1 行出现错误:
ORA-01843: 无效的月份
SQL> insert into o_test values(interval '30' year );
已创建 1 行。
SQL> insert into o_test values(interval '1199' month);
已创建 1 行。
SQL> select * from o_test;
VALUE
---------------------------------------------------------------------------
+10-05
+30-00
+99-11
PostgreSQL Interval
postgres=# create table p_test1(value interval);
CREATE TABLE
postgres=# insert into p_test1 values('100-5');
INSERT 0 1
postgres=# insert into p_test1 values('90-12');
错误: 间隔字段超出范围: "90-12"
第1行insert into p_test1 values('90-12');
^
postgres=# insert into p_test1 values('90-11');
INSERT 0 1
postgres=# insert into p_test1 values('90-0');
INSERT 0 1
postgres=# insert into p_test1 values('10000 year');
INSERT 0 1
postgres=# insert into p_test1 values('12000 month');
INSERT 0 1
postgres=# insert into p_test1 values('1000 year 12000 month');
INSERT 0 1
postgres=# select * from p_test1;
value
------------------
100 years 5 mons
90 years 11 mons
90 years
10000 years
1000 years
2000 years
(6 行记录)
2.2、Interval day(p) to second类型
Oracle的Interval day(p) to second类型表示日、时、分、秒的时间间隔。一起使用的时候，写法类似于Interval '12 5:6:7.8' day to second。其中的日期由精度p来决定，p值为0~9，默认为2。小时为0~23，分钟为0~59，秒为0~59。其实日、时、分、秒也都可以单独使用。单独使用时，日期，时、分、秒的值都可以使用精度p来控制。
PostgreSQL的Interval中，也有类似的用法，而且用法比Oracle灵活的多。
Oracle Interval day(p) to second
SQL> create table o_test(value interval day(3) to second);
表已创建。
SQL> insert into o_test values('1000 1:23:45');
insert into o_test values('1000 1:23:45')
第 1 行出现错误:
ORA-01873: 间隔的前导精度太小
SQL> insert into o_test values('999 1:23:45');
已创建 1 行。
SQL> insert into o_test values('100 24:50:45');
insert into o_test values('100 24:50:45')
第 1 行出现错误:
ORA-01850: 小时值必须介于 0 和 23 之间
SQL> insert into o_test values('100 23:60:45');
insert into o_test values('100 23:60:45')
第 1 行出现错误:
ORA-01851: 分钟值必须介于 0 和 59 之间
SQL> insert into o_test values('100 23:40:75');
insert into o_test values('100 23:40:75')
第 1 行出现错误:
ORA-01852: 秒值必须介于 0 和 59 之间
SQL> insert into o_test values(interval '2399' hour);
已创建 1 行。
SQL> insert into o_test values(interval '2400' hour);
insert into o_test values(interval '2400' hour)
第 1 行出现错误:
ORA-01873: 间隔的前导精度太小
SQL> insert into o_test values(interval '2400' hour(4));
已创建 1 行。
SQL> insert into o_test values(interval '800000' minute);
insert into o_test values(interval '800000' minute)
第 1 行出现错误:
ORA-01873: 间隔的前导精度太小
SQL> insert into o_test values(interval '800000' minute(6));
已创建 1 行。
SQL> insert into o_test values(interval '80000000' second);
insert into o_test values(interval '80000000' second)
第 1 行出现错误:
ORA-01873: 间隔的前导精度太小
SQL> insert into o_test values(interval '80000000' second(8));
已创建 1 行。
SQL> select * from o_test;
VALUE
---------------------------------------------------------------------------
+999 01:23:45.000000
+099 23:00:00.000000
+100 00:00:00.000000
+555 13:20:00.000000
+925 22:13:20.000000
PostgreSQL Interval
postgres=# create table p_test1(value interval);
CREATE TABLE
postgres=# insert into p_test1 values('1000 1:23:45');
INSERT 0 1
postgres=# insert into p_test1 values('100 50:23:45');
INSERT 0 1
postgres=# insert into p_test1 values('100 50:60:45');
错误: 间隔字段超出范围: "100 50:60:45"
第1行insert into p_test1 values('100 50:60:45');
^
postgres=# insert into p_test1 values('1000 day');
INSERT 0 1
postgres=# insert into p_test1 values('80000 hour');
INSERT 0 1
postgres=# insert into p_test1 values('8000000 minute');
INSERT 0 1
postgres=# insert into p_test1 values('800000000 second');
INSERT 0 1
postgres=# select * from p_test1;
value
--------------------
1000 days 01:23:45
100 days 50:23:45
1000 days
80000:00:00
133333:20:00
222222:13:20
(6 行记录)
Oracle的日期时间类型向PostgreSQL的数据迁移过程相对来说简单一些。由于PostgreSQL的数据类型的极值超越Oracle，因此，数据迁移过程中，只要根据Oracle的数据精度，在PostgreSQL中选择正确的数据类型。并留意一下二者SQL写法的不同，应该就能够完整正确的迁移过来。
参考文档：
PostgreSQL 9.4.4 中文手册：日期/时间类型
http://www.postgres.cn/docs/9.4/datatype-datetime.html
Database SQL Language Reference：Data Types
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#i45441
--------------------------------------------------------------
四：大数据类型
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类型。这是一种特殊的数据类型。数据并不保存在数据库内。数据库内仅仅是一个文件的指针。指明了文件的路径和文件名。实际的文件存储在硬盘上。只能对文件进行读取，而且不能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
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
--------------------------------------------------------------
五：其它类型
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
--------------------------------------------------------------