《卸甲筆記》-PostgreSQL和Oracle的SQL差異分析之五：函式的差異(一)

PostgreSQL是世界上功能最強大的開源資料庫，在國內得到了越來越多機構和開發者的青睞和應用。隨著PostgreSQL的應用越來越廣泛，Oracle向PostgreSQL資料庫的資料遷移需求也越來越多。資料庫之間資料遷移的時候，首先是遷移資料，然後就是SQL、儲存過程、序列等程式中不同的資料庫中資料的使用方式的轉換。下面根據自己的理解和測試，寫了一些SQL以及資料庫物件轉換方面的文章，不足之處，尚請多多指教。

1、NULL判斷函式

Oracle的NULL判斷函式是 nvl(A, B) 和 coalesce 兩個函式。nvl(A, B) 是判斷如果A不為NULL，則返回A, 否則返回B。引數需要是相同型別的，或者可以自動轉換成相同型別的, 否則需要顯式轉換。而 coalese 引數可以有多個，返回第一個不為NULL的引數。而引數必須為相同型別的 ，不會自動轉換。
PostgreSQL中沒有nvl函式。但是有coalesce函式。用法和Oracle的一樣。可以使用coalesce來轉換Oracle的nvl和coalesce。引數需要使用相同型別，或者可以轉換成相同型別的。否則需要手動轉換。

Oracle NULL判斷函式

SQL> select * from o_test;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
           111111     05-8月 -16
         1            31-7月 -16
         2 222222

SQL> select nvl(value1, `Hello`) value1 from o_test;
select nvl(value1, `Hello`) value1 from o_test
                   *
第 1 行出現錯誤:
ORA-01722: 無效數字

SQL> select nvl(value1, `10000`) value1 from o_test;

    VALUE1
----------
     10000
         1
         2

SQL> select nvl(value2, `Hello`) value2 from o_test;

VALUE2
----------
111111
Hello
222222

SQL> select nvl(value3, `2010-1-1`) value3 from o_test;
select nvl(value3, `2010-1-1`) value3 from o_test
                   *
第 1 行出現錯誤:
ORA-01861: 文字與格式字串不匹配

SQL> select nvl(value3, to_date( `2010-01-01`,`YYYY-MM-DD`)) value3 from o_test;

VALUE3
--------------
05-8月 -16
31-7月 -16
01-1月 -10

SQL> select coalesce(value1, `10000`) value1 from o_test;
select coalesce(value1, `10000`) value1 from o_test
                        *
第 1 行出現錯誤:
ORA-00932: 資料型別不一致: 應為 NUMBER, 但卻獲得 CHAR

SQL> select coalesce(value1, 10000) value1 from o_test;

    VALUE1
----------
     10000
         1
         2

SQL> select coalesce(value2, ``,  `Hello John`) value2 from o_test;

VALUE2
----------
111111
Hello John
222222

SQL> select coalesce(value3,``, to_date( `2010-01-01`,`YYYY-MM-DD`)) value3 from o_test;
select coalesce(value3,``, to_date( `2010-01-01`,`YYYY-MM-DD`)) value3 from o_test
                       *
第 1 行出現錯誤:
ORA-00932: 資料型別不一致: 應為 DATE, 但卻獲得 CHAR

SQL> select coalesce(value3, null, to_date( `2010-01-01`,`YYYY-MM-DD`)) value3 from o_test;

VALUE3
--------------
05-8月 -16
31-7月 -16
01-1月 -10

PostgreSQL NULL判斷函式

postgres=# select * from p_test;
 value1 | value2 |       value3
--------+--------+---------------------
        | 11111  | 2010-01-01 00:00:00
      1 |        | 2010-01-01 00:00:00
      2 | 22222  |
(3 行記錄)

postgres=# select coalesce(value1, `Hello`) value1  from p_test;
錯誤:  無效的整數型別輸入語法: "Hello"
第1行select coalesce(value1, `Hello`) value1  from p_test;
                             ^
postgres=# select coalesce(value1, `10000`) value1  from p_test;
 value1
--------
  10000
      1
      2
(3 行記錄)

postgres=# select coalesce(value2, null, `Hello world`) value2  from p_test;
   value2
-------------
 11111
 Hello world
 22222
(3 行記錄)

postgres=# select coalesce(value3, null, `2012-10-10`) value3  from p_test;
       value3
---------------------
 2010-01-01 00:00:00
 2016-08-05 10:01:32
 2012-10-10 00:00:00
(3 行記錄)

postgres=# select coalesce(value3, null, `2012-10-A`) value3  from p_test;
錯誤:  無效的型別 timestamp 輸入語法: "2012-10-A"
第1行select coalesce(value3, null, `2012-10-A`) value3  from p_te...
                                   ^

2、字串連線

2.1、字串連線符( || )

Oracle的字串連線符(||) 和 PostgreSQL的字串連線符(||)的用法基本相同，不同的地方是
1、當連線的引數有null的時候，Oracle中，null 的連線效果類似於空字串(“)，而PostgreSQL中， 連線的引數中有null的， 連線結果統一都是null。
2、當幾個引數都是數字的時候，Oracle會自動把數字轉換為字串。這個和Oracle內部的自動型別轉換有關係。而PostgreSQL中，幾個引數中至少有一個應該為字串，否則會報錯。

資料遷移的時候，對於Oracle的 A || B 可以使用PostgreSQL的coalesce( A, “) || coalesce( B, “)形式來轉換。

Oracle 字串連線符( || )

SQL> select `abc` || `def` value from dual;

VALUE
------
abcdef

SQL> select 123 || 456 value from dual;

VALUE
------
123456

SQL> select null || 456 value from dual;

VAL
---
456

SQL> select null || `abcdef`  value from dual;

VALUE
------
abcdef

SQL> select length(null || null) value from dual;

     VALUE
----------

PostgreSQL 字串連線符( || )

postgres=# select `abc` || `def` as value;
 value
--------
 abcdef
(1 行記錄)

postgres=# select 123 || 456 as  value;
錯誤:  操作符不存在: integer || integer
第1行select 123 || 456 as  value;
                ^
提示:  沒有匹配指定名稱和引數型別的操作符. 您也許需要增加明確的型別轉換.
postgres=# select 123||`456` as value;
 value
--------
 123456
(1 行記錄)

postgres=#  select null || 456 as  value ;
 value
-------

(1 行記錄)

postgres=# select null || `abcdef`  as value;
 value
-------

(1 行記錄)

postgres=# select length(null || null) as value ;
 value
-------

(1 行記錄)

2.2、字串連線函式concat

Oracle的concat函式類似於字串連線符（||），但只能夠連線兩個引數。引數需要是字串型別，或者可以自動轉換成字串型別。
PostgreSQL中也內建了這個方法。
需要注意的是，Oracle的concat，如果兩個引數都是null, 則結果是null。而PostgreSQL中，如果兩個引數都是null，則 結果是空字串(“)。因為PostgreSQL的concat方法內部對於引數做了coalesce(null, “)處理。

Oracle concat

SQL> select concat(`abc`,`def`) from dual;

CONCAT
------
abcdef

SQL> select concat(123, 456) from dual;

CONCAT
------
123456

SQL> select concat(null, 456) value from dual;

VAL
---
456

SQL> select concat(null, `abc`) value from dual;

VAL
---
abc

SQL> select concat(null, null) value from dual;

V
-


SQL> select length(concat(null, null)) value from dual;

     VALUE
----------

SQL> select * from o_test;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
           111111     05-8月 -16
         1            31-7月 -16
         2 222222

SQL> select concat(value3, value2) from o_test;

CONCAT(VALUE3,VALUE2)
------------------------
05-8月 -16111111
31-7月 -16
222222

PostgreSQL 字串連線函式

postgres=# select concat(`abc`,`def`);
 concat
--------
 abcdef
(1 行記錄)

postgres=# select concat(123, 456);
 concat
--------
 123456
(1 行記錄)

postgres=# select concat(null, 456) as value;
 value
-------
 456
(1 行記錄)


postgres=# select concat(null, `abc`) as value;
 value
-------
 abc
(1 行記錄)

postgres=# select concat(null, null) as value;
 value
-------

(1 行記錄)

postgres=# select length(concat(null, null)) as value;
 value
-------
     0
(1 行記錄)

postgres=# select * from p_test;
 value1 | value2 |       value3
--------+--------+---------------------
        | 11111  | 2010-01-01 00:00:00
      1 |        | 2016-08-05 10:01:32
      2 | 22222  |
(3 行記錄)

postgres=# select concat(value3, value2)   as value from p_test;
          value
--------------------------
 2010-01-01 00:00:0011111
 2016-08-05 10:01:32
 22222
(3 行記錄)