PostgreSQL與Oracle在資料比較上存在差異,本節簡單介紹PostgreSQL的資料型別轉換規則.

### 一、概述
PostgreSQL比起其他資料庫有更強和更靈活的擴充套件型別系統.先前介紹的PG的詞法和語法分析的掃描器/分析器把詞法元素拆分為5種型別:integers(整型), non-integer numbers(非整型數字), strings(字串), identifiers(識別符號), 和key words(關鍵字),大多數的非數字型別會首先歸類為字串.在SQL中可透過指定型別名稱從而讓語法分析器"走在"正確的解析路徑上. 
如:
```
testdb=# SELECT text 'Origin' AS "label", point '(0,0)' AS "value";
 label  | value 
--------+-------
 Origin | (0,0)
(1 row)
```
上述SQL有兩個字面(literal)常量,型別分別是text和point.對於字串文字(literal)如果沒有指定型別,該佔位符(placeholder)的型別會認為是**unknown**,在後續解析階段在確定具體的型別.
在PG的分析器中,有4中基本的SQL結構要求有唯一的型別轉換規則,分別是:
**Function calls**
PostgreSQL支援函式過載,函式名稱並不唯一,因此要求基於提供的引數型別確定相應的函式.
**Operators**
PostgreSQL允許一元&二元運算子,與函式一樣,運算子也支援過載,也需要基於提供的引數型別確定對應的運算子.
**Value Storage**
INSERT&UPDATE語句中的表示式必須與目標列型別一致或者可轉換為目標列型別.
**UNION, CASE, and related constructs**
UNION型別的結果必須匹配且能轉換為統一的集合.CASE和其他相關的結構與此類似.

系統目錄儲存了資料型別之間如何(強制)轉換以及如何執行這些轉換的資訊,可以透過CREATE CASE自定義強制型別轉換.
資料型別劃分為幾個級別的型別目錄(categories),包括boolean, numeric, string, bitstring, datetime, timespan, geometric, network, 和 user-defined型別.在每一個目錄內部,有1個或多個首選型別,透過仔細選擇首選型別和可用的隱式強制轉換,可以確保以一種有用的方式處理表示式歧義.
所有的轉換規則遵循以下的原則:
1.隱式轉換永遠不應該產生不可預測的結果
2.不需要隱式轉換時不應有額外的分析或執行負載
3.某個查詢語句中的函式需要隱式轉換,如使用者定義了不需要隱式轉換的函式,分析器應使用新函式而不應使用舊函式

### 二、運算子
運算子表示式引用的特定運算子使用以下過程確定,注意該過程間接受相關運算子優先順序的影響,因為這會確定那個子表示式將會視為那個運算子的輸入.
**Operator Type Resolution**
1.在pg_operator系統目錄中選擇哪些運算子.通常情況下,如果運算子不指定schema,則在當前搜尋路徑中選擇名稱&引數個數匹配的運算子,否則將選擇指定schema的運算子.
a.如在該搜尋路徑中存在多個相同引數型別的運算子,則選擇最早出現的那個.對於具有不同引數型別的運算子,無論搜尋路徑如何設定,都被認為是平等的.
2.檢查運算子是否接受輸入引數型別.如存在,則使用此運算子.
a.如果二元運算子中的一個引數為unknown型別,假定該引數與本次檢查中的另外一個引數型別相同.在此步驟中,如一/二元運算子的一/兩個引數型別都是unknown,那麼將找不到匹配的運算子.
b.如果二進位制運算子呼叫的一個引數是unknown,另外一個引數是域型別,那麼接下來檢查是否有一個運算子在兩邊都接受域的基本型別.
3.尋找最佳匹配
a.丟棄輸入引數型別不匹配以及不能轉換(隱式)的運算子.unknown literals假定可轉換為任意型別.如只剩下一個候選運算子,則結束,否則繼續下一個步驟.
b.如果其中一個輸入引數是域型別,把此引數視為後續步驟的域基型別.確保該域與域基型別一致以消除歧義.
c.遍歷所有候選項,只保留在輸入型別上精確匹配的那些運算子.如無精確匹配,則保留所有候選.如只剩下一個,則使用此運算子,否則下一步驟.
d.遍歷所有候選項,並保留那些在大多數位置需要型別轉換並接受首選型別的候選項.如無精確匹配,則保留所有候選.如只剩下一個後續,則使用此運算子,否則下一步驟.
e.如所有輸入引數型別均為unknown,則使用剩下的候選項檢查在這些引數位置上接受這些引數的型別目錄.在每一個位置上,如所有候選接受該目錄,則選擇*string*目錄.否則,如果索引剩餘候選項接受相同的型別目錄,則選擇此目錄;否則會由於正確的選項不能規約而失敗.現在丟棄那些不接受已選型別目錄的候選項.更進一步,如果所有候選項接受目錄中的首選型別,丟棄接受該引數非首選型別的候選項.如經此檢查後無候選項留下,則保留所有的候選項,如只剩下一個,則使用這個,否則繼續下一步
f.如既存在unknown和已知引數型別,所有已知型別均為同一個型別,假定*unknown*引數也是這種型別,檢查哪個候選項可接受在unknown引數.如存在這麼一個候選項,則使用該候選項,否則失敗.

幾個例子:
*階乘*
```
testdb=# SELECT 40 ! AS "40 factorial";
                   40 factorial                   
--------------------------------------------------
 815915283247897734345611269596115894272000000000
(1 row)
```
一元運算子"!"在標準目錄中定義的引數型別為bigint,掃描器會對輸入引數的型別視為integer,因此把引數40轉換為bigint.

*字串拼接*
```
testdb=# SELECT text 'abc' || 'def' AS "text and unknown";
 text and unknown 
------------------
 abcdef
(1 row)
```
一邊是text,一邊是unknown,則假定第二個引數'def'型別為text.
```
testdb=# SELECT 'abc' || 'def' AS "unspecified";
 unspecified 
-------------
 abcdef
(1 row)
```
在這種情況下,兩邊都是unknown,解析器查詢所有候選運算子,發現候選運算子同時接受(bit-)string-category輸入,由於字串是首選的,則選擇該類別,然後把該類比首選型別text作為特定型別來解析unknown型別的引數.

*取絕對值和位取反運算*
```
testdb=# SELECT @ '-4.5' AS "abs";
 abs 
-----
 4.5
(1 row)
```
取絕對值運算可接受多個輸入型別,在數值category中float8是首選型別,因此PG會使用此條目來處理unknown引數型別.
在應用選中的運算子前,系統會隱式處理unknown型別字面量為float8,因此係統會驗證輸入是否為float8型別,不符合的則報錯.
```
testdb=# SELECT @ '-4.5e500' AS "abs";
psql: ERROR:  "-4.5e500" is out of range for type double precision
LINE 1: SELECT @ '-4.5e500' AS "abs";
                 ^
```
另一方面,取反運算子\~只接受整型型別作為引數,如引數型別為unknown則被視為float8,執行會出錯:
```
testdb=# SELECT ~ '20' AS "negation";
psql: ERROR:  operator is not unique: ~ unknown
LINE 1: SELECT ~ '20' AS "negation";
               ^
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.
```

**Function/Value Storage/UNION, CASE, and related constructs下一節再行介紹**

### 三、參考資料
[PostgreSQL Type Conversion](https://www.postgresql.org/docs/11/typeconv-overview.html)

Oracle vs PostgreSQL，研發注意事項（9）- PostgreSQL資料型別轉換規則#1

相關文章