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