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