Oracle vs PostgreSQL，研發(fā)注意事項(xiàng)（9）- PostgreSQL數(shù)據(jù)類型轉(zhuǎn)換規(guī)則#1

PostgreSQL與Oracle在數(shù)據(jù)比較上存在差異,本節(jié)簡單介紹PostgreSQL的數(shù)據(jù)類型轉(zhuǎn)換規(guī)則.

一、概述

PostgreSQL比起其他數(shù)據(jù)庫有更強(qiáng)和更靈活的擴(kuò)展類型系統(tǒng).先前介紹的PG的詞法和語法分析的掃描器/分析器把詞法元素拆分為5種類型:integers(整型), non-integer numbers(非整型數(shù)字), strings(字符串), identifiers(標(biāo)識符), 和key words(關(guān)鍵字),大多數(shù)的非數(shù)字類型會首先歸類為字符串.在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)的類型會認(rèn)為是 unknown ,在后續(xù)解析階段在確定具體的類型.
在PG的分析器中,有4中基本的SQL結(jié)構(gòu)要求有唯一的類型轉(zhuǎn)換規(guī)則,分別是:
Function calls
PostgreSQL支持函數(shù)重載,函數(shù)名稱并不唯一,因此要求基于提供的參數(shù)類型確定相應(yīng)的函數(shù).
Operators
PostgreSQL允許一元&二元操作符,與函數(shù)一樣,操作符也支持重載,也需要基于提供的參數(shù)類型確定對應(yīng)的操作符.
Value Storage
INSERT&UPDATE語句中的表達(dá)式必須與目標(biāo)列類型一致或者可轉(zhuǎn)換為目標(biāo)列類型.
UNION, CASE, and related constructs
UNION類型的結(jié)果必須匹配且能轉(zhuǎn)換為統(tǒng)一的集合.CASE和其他相關(guān)的結(jié)構(gòu)與此類似.

系統(tǒng)目錄存儲了數(shù)據(jù)類型之間如何(強(qiáng)制)轉(zhuǎn)換以及如何執(zhí)行這些轉(zhuǎn)換的信息,可以通過CREATE CASE自定義強(qiáng)制類型轉(zhuǎn)換.
數(shù)據(jù)類型劃分為幾個級別的類型目錄(categories),包括boolean, numeric, string, bitstring, datetime, timespan, geometric, network, 和 user-defined類型.在每一個目錄內(nèi)部,有1個或多個首選類型,通過仔細(xì)選擇首選類型和可用的隱式強(qiáng)制轉(zhuǎn)換,可以確保以一種有用的方式處理表達(dá)式歧義.
所有的轉(zhuǎn)換規(guī)則遵循以下的原則:
1.隱式轉(zhuǎn)換永遠(yuǎn)不應(yīng)該產(chǎn)生不可預(yù)測的結(jié)果
2.不需要隱式轉(zhuǎn)換時不應(yīng)有額外的分析或執(zhí)行負(fù)載
3.某個查詢語句中的函數(shù)需要隱式轉(zhuǎn)換,如用戶定義了不需要隱式轉(zhuǎn)換的函數(shù),分析器應(yīng)使用新函數(shù)而不應(yīng)使用舊函數(shù)

二、操作符

操作符表達(dá)式引用的特定操作符使用以下過程確定,注意該過程間接受相關(guān)操作符優(yōu)先級的影響,因?yàn)檫@會確定那個子表達(dá)式將會視為那個操作符的輸入.
Operator Type Resolution
1.在pg_operator系統(tǒng)目錄中選擇哪些操作符.通常情況下,如果操作符不指定schema,則在當(dāng)前搜索路徑中選擇名稱&參數(shù)個數(shù)匹配的操作符,否則將選擇指定schema的操作符.
a.如在該搜索路徑中存在多個相同參數(shù)類型的操作符,則選擇最早出現(xiàn)的那個.對于具有不同參數(shù)類型的操作符,無論搜索路徑如何設(shè)置,都被認(rèn)為是平等的.
2.檢查操作符是否接受輸入?yún)?shù)類型.如存在,則使用此操作符.
a.如果二元操作符中的一個參數(shù)為unknown類型,假定該參數(shù)與本次檢查中的另外一個參數(shù)類型相同.在此步驟中,如一/二元操作符的一/兩個參數(shù)類型都是unknown,那么將找不到匹配的操作符.
b.如果二進(jìn)制操作符調(diào)用的一個參數(shù)是unknown,另外一個參數(shù)是域類型,那么接下來檢查是否有一個操作符在兩邊都接受域的基本類型.
3.尋找最佳匹配
a.丟棄輸入?yún)?shù)類型不匹配以及不能轉(zhuǎn)換(隱式)的操作符.unknown literals假定可轉(zhuǎn)換為任意類型.如只剩下一個候選操作符,則結(jié)束,否則繼續(xù)下一個步驟.
b.如果其中一個輸入?yún)?shù)是域類型,把此參數(shù)視為后續(xù)步驟的域基類型.確保該域與域基類型一致以消除歧義.
c.遍歷所有候選項(xiàng),只保留在輸入類型上精確匹配的那些操作符.如無精確匹配,則保留所有候選.如只剩下一個,則使用此操作符,否則下一步驟.
d.遍歷所有候選項(xiàng),并保留那些在大多數(shù)位置需要類型轉(zhuǎn)換并接受首選類型的候選項(xiàng).如無精確匹配,則保留所有候選.如只剩下一個后續(xù),則使用此操作符,否則下一步驟.
e.如所有輸入?yún)?shù)類型均為unknown,則使用剩下的候選項(xiàng)檢查在這些參數(shù)位置上接受這些參數(shù)的類型目錄.在每一個位置上,如所有候選接受該目錄,則選擇 string 目錄.否則,如果索引剩余候選項(xiàng)接受相同的類型目錄,則選擇此目錄;否則會由于正確的選項(xiàng)不能規(guī)約而失敗.現(xiàn)在丟棄那些不接受已選類型目錄的候選項(xiàng).更進(jìn)一步,如果所有候選項(xiàng)接受目錄中的首選類型,丟棄接受該參數(shù)非首選類型的候選項(xiàng).如經(jīng)此檢查后無候選項(xiàng)留下,則保留所有的候選項(xiàng),如只剩下一個,則使用這個,否則繼續(xù)下一步
f.如既存在unknown和已知參數(shù)類型,所有已知類型均為同一個類型,假定 unknown 參數(shù)也是這種類型,檢查哪個候選項(xiàng)可接受在unknown參數(shù).如存在這么一個候選項(xiàng),則使用該候選項(xiàng),否則失敗.

幾個例子:
階乘

testdb=# SELECT 40 ! AS "40 factorial";
                   40 factorial                   
--------------------------------------------------
 815915283247897734345611269596115894272000000000
(1 row)

一元運(yùn)算符”!”在標(biāo)準(zhǔn)目錄中定義的參數(shù)類型為bigint,掃描器會對輸入?yún)?shù)的類型視為integer,因此把參數(shù)40轉(zhuǎn)換為bigint.

字符串拼接

testdb=# SELECT text 'abc' || 'def' AS "text and unknown";
 text and unknown 
------------------
 abcdef
(1 row)

一邊是text,一邊是unknown,則假定第二個參數(shù)’def’類型為text.

testdb=# SELECT 'abc' || 'def' AS "unspecified";
 unspecified 
-------------
 abcdef
(1 row)

在這種情況下,兩邊都是unknown,解析器查找所有候選操作符,發(fā)現(xiàn)候選操作符同時接受(bit-)string-category輸入,由于字符串是首選的,則選擇該類別,然后把該類比首選類型text作為特定類型來解析unknown類型的參數(shù).

取絕對值和位取反運(yùn)算

testdb=# SELECT @ '-4.5' AS "abs";
 abs 
-----
 4.5
(1 row)

取絕對值運(yùn)算可接受多個輸入類型,在數(shù)值category中float8是首選類型,因此PG會使用此條目來處理unknown參數(shù)類型.
在應(yīng)用選中的操作符前,系統(tǒng)會隱式處理unknown類型字面量為float8,因此系統(tǒng)會驗(yàn)證輸入是否為float8類型,不符合的則報(bào)錯.

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";
                 ^

另一方面,取反運(yùn)算符~只接受整型類型作為參數(shù),如參數(shù)類型為unknown則被視為float8,執(zhí)行會出錯:

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下一節(jié)再行介紹

三、參考資料

PostgreSQL Type Conversion