《卸甲筆記》-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
6r8`, 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
6r8
         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
6r8
         1 12e34
           6r8

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

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
         1 12e34
6r8

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, `

`, null);
INSERT 0 1
postgres=# insert into p_test values(1, E`ab

c`, 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 | 

      |
      1 | ab       +|
        | 
c       |
      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 | 

      |
      1 | ab       +|
        | 
c       |
      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 | 

   |
(1 行記錄)