《卸甲笔记》-PostgreSQL和Oracle的SQL差异分析之四：特殊字符和符号

PostgreSQL是世界上功能最强大的开源数据库，在国内得到了越来越多机构和开发者的青睐和应用。随着PostgreSQL的应用越来越广泛，Oracle向PostgreSQL数据库的数据迁移需求也越来越多。数据库之间数据迁移的时候，首先是迁移数据，然后就是SQL、存储过程、序列等程序中不同的数据库中数据的使用方式的转换。下面根据自己的理解和测试，写了一些SQL以及数据库对象转换方面的文章，不足之处，尚请多多指教。

空字符串( '' )

Oracle中，空字符串( '' )很多时候是和null同样处理的。给varchar2和char类型赋值的时候按照null处理。在给日期类型或者数字类型赋值的时候，也是按照null处理。但是在where条件部分，=‘’和 is null 是不同的。

PostgreSQL里面，空字符串( '' )和 null是不同的。完全是不同的处理。转换SQL的时候，一定要注意。

Oracle 空字符串

SQL> create table o_test(value1 number, value2 varchar2(10), value3 date);

表已创建。

SQL> insert into o_test values('', '11111', to_date( '2010-01-01','YYYY-MM-DD'));

已创建 1 行。

SQL> insert into o_test values(1, '', to_date( '2010-01-01','YYYY-MM-DD'));

已创建 1 行。

SQL> insert into o_test values(2, '22222', to_date( '','YYYY-MM-DD'));

已创建 1 行。

SQL> select * from o_test;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
           11111      01-1月 -10
         1            01-1月 -10
         2 22222

SQL> select * from o_test where value1 = '';

未选定行

SQL> select * from o_test where value1 is null;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
           11111      01-1月 -10

SQL> select * from o_test where value2 = '';

未选定行

SQL> select * from o_test where value2 is null;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
         1            01-1月 -10

SQL> select * from o_test where value3 = '';

未选定行

SQL> select * from o_test where value3 is null;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
         2 22222

PostgreSQL 空字符串

postgres=# create table p_test(value1 integer, value2 varchar(10), value3 timestamp(0) without time zone);
CREATE TABLE
postgres=# insert into p_test values('', '11111', to_timestamp('2010-01-01', 'YYYY-MM-DD'));
错误:  无效的整数类型输入语法: ""
第1行insert into p_test values('', '11111', to_timestamp('2010-01...
                               ^
postgres=#  insert into p_test values(null, '11111', to_timestamp('2010-01-01', 'YYYY-MM-DD'));
INSERT 0 1
postgres=# insert into p_test values(1, '', to_timestamp('2010-01-01', 'YYYY-MM-DD'));
INSERT 0 1
postgres=# insert into p_test values(2, '22222', to_timestamp('', 'YYYY-MM-DD'));
INSERT 0 1
postgres=# select * from p_test;
 value1 | value2 |         value3
--------+--------+------------------------
        | 11111  | 2010-01-01 00:00:00
      1 |        | 2010-01-01 00:00:00
      2 | 22222  | 0001-01-01 00:00:00 BC
(3 行记录)

postgres=# select * from p_test where value1 = '';
错误:  无效的整数类型输入语法: ""
第1行select * from p_test where value1 = '';
                                         ^

postgres=# select * from p_test where value1 is null;
 value1 | value2 |       value3
--------+--------+---------------------
        | 11111  | 2010-01-01 00:00:00
(1 行记录)

postgres=# select * from p_test where value2 =  '';
 value1 | value2 |       value3
--------+--------+---------------------
      1 |        | 2010-01-01 00:00:00
(1 行记录)

postgres=# select * from p_test where value2 is null;
 value1 | value2 | value3
--------+--------+--------
(0 行记录)

postgres=# select * from p_test where value3 is null;
 value1 | value2 | value3
--------+--------+--------
(0 行记录)

postgres=# select * from p_test where to_char(value3, 'YYYY-MM-DD') ='0001-01-01';
 value1 | value2 |         value3
--------+--------+------------------------
      2 | 22222  | 0001-01-01 00:00:00 BC
(1 行记录)

比较运算符

Oracle中，比较运算符之间是可以有空格的。比如【> = 】这样的写法是允许的。而PostgreSQL中，运算符之间不能有空格。

Oracle 比较运算符

SQL> select * from o_test where value1 > = 2;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
         2 22222

SQL> select * from o_test where value1 <                         = 3;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
         1            01-1月 -10
         2 22222

PostgreSQL 比较运算符

postgres=# select * from p_test where value1 > = 2;
错误:  语法错误 在 "=" 或附近的
第1行select * from p_test where value1 > = 2;
                                         ^
postgres=# select * from p_test where value1 >= 2;
 value1 | value2 |         value3
--------+--------+------------------------
      2 | 22222  | 0001-01-01 00:00:00 BC
(1 行记录)

postgres=# select * from p_test where value1 < = 3;
错误:  语法错误 在 "=" 或附近的
第1行select * from p_test where value1 < = 3;
                                         ^
postgres=# select * from p_test where value1 <= 3;
 value1 | value2 |         value3
--------+--------+------------------------
      1 |        | 2010-01-01 00:00:00
      2 | 22222  | 0001-01-01 00:00:00 BC
(2 行记录)

逃逸字符

逃逸的意思是有特殊意义的字符，前面如果加上逃逸字符的话，就不代表它的特殊含义，而代表它的字符本意。
Oracle的SQL中，使用( ' )来逃逸它本身。Oracle没有其它的标准逃逸字符。比如"\n"并不代表回车，而是代表它的本意的"\n"两个字符。如果输入回车的话，使用chr(10)来表示。在正则表达式等需要使用特殊字符本意的时候，使用关键字(escape)后面定义的字符进行逃逸。

PostgreSQL的SQL，也支持使用( ' )来逃逸它本身。老版的还支持使用反斜杠( \ )， 但新版已经不使用。Oracle定义了标准的逃逸字串（E'XXX'） 格式。 在正则表达式等需要使用特殊字符本意的时候，默认使用"\"做逃逸字串。也可以使用关键字(escape)后面定义的字符进行逃逸。

数据迁移的时候，老版的PostgreSQL中，需要对逃逸字串进行特殊处理。比如把"\“替换成"\\"。新版已经不再需要。可以直接按照Oracle的方式直接转换。

Oracle 逃逸字符

SQL> insert into o_test values(1, '12e34'6r8', null);
ERROR:
ORA-01756: 引号内的字符串没有正确结束

SQL> insert into o_test values(1, '12e34''6r8', null);

已创建 1 行。

SQL> insert into o_test values(1, '12e34\n6r8', null);

已创建 1 行。

SQL> insert into o_test values(1, '12e34' || chr(10) || '6r8', null);

已创建 1 行。

SQL> select * from o_test;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
         1 1234_678
         1 12e34%6r8
         1 12e34'6r8
         1 12e34\n6r8
         1 12e34
           6r8

SQL> select * from o_test where value2 like '%_%';

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
         1 1234_678
         1 12e34%6r8
         1 12e34'6r8
         1 12e34\n6r8
         1 12e34
           6r8

SQL> select * from o_test where value2 like '%\_%';

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
         1 12e34\n6r8

SQL> select * from o_test where value2 like '%\_%' escape '\' ;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
         1 1234_678

SQL> select * from o_test where value2 like '%r_%' escape 'r' ;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
         1 1234_678

PostgreSQL 逃逸字符

postgres=#  insert into p_test values(1, '12e34'6r8', null);
postgres'# ');
错误:  语法错误 在 "6" 或附近的
第1行insert into p_test values(1, '12e34'6r8', null);
                                         ^
postgres=#  insert into p_test values(1, '12e34''6r8', null);
INSERT 0 1
postgres=#  insert into p_test values(1, '\n\r', null);
INSERT 0 1
postgres=# insert into p_test values(1, E'ab\n\rc', null);
INSERT 0 1
postgres=# insert into p_test values(1, 'ab'|| chr(10) || 'c', null);
INSERT 0 1
postgres=# select * from p_test;
 value1 |  value2   | value3
--------+-----------+--------
      1 | 1234_678  |
      1 | 12e34%6r8 |
      1 | 12e34'6r8 |
      1 | \n\r      |
      1 | ab       +|
        | \rc       |
      1 | ab       +|
        | c         |
(6 行记录)

postgres=# select * from p_test where value2 like '%_%';
 value1 |  value2   | value3
--------+-----------+--------
      1 | 1234_678  |
      1 | 12e34%6r8 |
      1 | 12e34'6r8 |
      1 | \n\r      |
      1 | ab       +|
        | \rc       |
      1 | ab       +|
        | c         |
(6 行记录)

postgres=# select * from p_test where value2 like '%\_%';
 value1 |  value2  | value3
--------+----------+--------
      1 | 1234_678 |
(1 行记录)

postgres=# select * from p_test where value2 like '%r_%' escape 'r' ;
 value1 |  value2  | value3
--------+----------+--------
      1 | 1234_678 |
(1 行记录)

postgres=# select * from p_test where value2 like '%\\%';
 value1 | value2 | value3
--------+--------+--------
      1 | \n\r   |
(1 行记录)