868
技術社區[雲棲]
PostgreSQL SQL 語言：類型轉換
本文檔為PostgreSQL 9.6.0文檔，本轉載已得到原譯者彭煜瑋授權。
SQL是一種強類型語言。也就是說，每個數據項都有一個相關的數據類型，數據類型決定其行為和允許的用法。 PostgreSQL有一個可擴展的類型係統，該係統比其它SQL實現更具通用和靈活。因而，PostgreSQL中大多數類型轉換行為是由通用規則來管理的，而不是ad hoc啟發式規則。這種做法允許使用混合類型表達式，即便是其中包含用戶定義的類型。
PostgreSQL掃描器/解析器隻將詞法元素分解成五個基本種類：整數、非整數數字、字符串、標識符、關鍵字。大多數非數字類型常量首先被分類為字符串。SQL語言定義允許將類型名指定為字符串， 這個機製被PostgreSQL用於保證解析器沿著正確的方向運行。例如，查詢：
SELECT text 'Origin' AS "label", point '(0,0)' AS "value";
label | value
--------+-------
Origin | (0,0)
(1 row)
有兩個文字常量，類型分別為text和point。如果一個串文字沒有指定類型，初始將被分配一個占位符類型unknown，該類型將在下文描述的後續階段被解析。
在SQL解析器裏，有四種基本的SQL結構要求獨立的類型轉換規則：
函數調用
PostgreSQL類型係統的大部分建立在一套豐富的函數上。 函數可以有一個或多個參數。由於PostgreSQL允許函數重載， 所以函數名自身並不唯一地標識將要被調用的函數，解析器必須根據提供的參數類型選擇正確的函數。
操作符
PostgreSQL允許帶有前綴和後綴一元（單目）操作符的表達式，也允許二元（兩個參數）操作符。像函數一樣，操作符也可以被重載，因此操作符的選擇也有同樣的問題。
值存儲
SQL INSERT和UPDATE語句將表達式的結果放 入表中。語句中的表達式類型必須和目標列的類型一致（或者可以被轉換為一致）。
UNION、CASE和相關結構
因為來自一個聯合的SELECT語句中的所有查詢結果必須在一個列集中顯示，所以每個 SELECT子句的結果類型必須能相互匹配並被轉換成一個統一的集合。類似地，一個 CASE結構的結果表達式必須被轉換成一種公共的類型，這樣CASE表達式作為整體才 有一種已知的輸出類型。同樣的要求也存在於ARRAY結構以及GREATEST和LEAST函數中。
係統目錄存儲有關哪些數據類型之間存在哪種轉換（或造型）以及如何執行這些轉換的相關信息。額外的造型可以由用戶通過CREATE CAST命令增加（這個通常和定義一種新的數據類型一起完成。 內建的類型轉換集已經經過了仔細的雕琢，最好不要去更改它們）。
解析器提供了一種額外的啟發式規則，它允許在具有隱式造型的類型組中恰當造型行為的改進決定。 數據類型被分為幾個基本的類型分類，包括boolean、numeric、string、bitstring、datetime、timespan、geometric、network和用戶自定義（可參閱Table 50-56中的列表；但需要注意的是 也可以創建自定義的類型分類）。在每個分類中，可以有一個或多個首選類型， 當存在類型選擇時，這個是更好的選擇。利用精心選擇的首選類型和可用的隱式造型， 我們可以確保有歧義的表達式（那些有多個候選解析方案的表達式）可以用一種有用的方式來處理。
所有類型轉換規則都是建立在下麵幾個基本原則上的：
- 隱式轉換決不能有意外的或不可預見的輸出。
- 如果一個查詢不需要隱式類型轉換，解析器或執行器不應該有額外的開銷。也就是說，如果一個查詢是結構良好的並且類型已經匹配，則查詢不應該在解析器裏耗費額外的時間執行，也不會在查詢中引入不必要的隱式類型轉換調用。
- 另外，如果一個查詢通常要求為某個函數進行隱式類型轉換，而用戶定義了一個有正確參數類型的新函數， 解析器應該使用新函數並不再做隱式轉換來使用舊函數。
被一個操作符表達式引用的特定操作符由下列過程決定。注意這個過程會被所涉及的操作符的優先級間接地影響，因為這將決定哪些子表達式被用作哪個操作符的輸入。詳見Section 4.1.6。
操作符類型決定
1.從係統目錄pg_operator中選出要考慮的操作符。如果使用了一個不帶模式限定的操作符 名（常見的情況），那麼操作符被認為是那些在當前搜索路徑中可見並有匹配的名字和參數個數的操作符。如果給出一個被限定的操作符名，那麼隻考慮指定模式中的操作符。
a.如果搜索路徑找到了多個有相同參數類型的操作符，那麼隻考慮最早出現在路徑中的那一個。 但是不同參數類型的操作符將被平等看待，而不管它們在路徑中的位置如何。
2.查找一個正好接受輸入參數類型的操作符。如果找到一個（在一組被考慮的操作符中，可能隻存在一個正好匹配的），則使用之。
a.如果一個二元操作符調用中的一個參數是unknown類型，則在本次檢查中假設它與另一個參數類型相同。 對於涉及兩個unknown輸入的調用或者帶有一個unknown輸入的一元操作符，在這一步將永遠找不到一個匹配。
b.如果一個二元操作符調用的其中一個參數是unknown類型 而另一個是一種域類型，下一次檢查會看看是否有一個操作符正好在兩邊都 接受該域的基類型，如果有就使用它。
3.尋找最優匹配。
a.拋棄那些輸入類型不匹配並且也不能被轉換成匹配的候選操作符。 unknown文字被假定為可以為這個目的被轉換為 任何東西。如果隻剩下一個候選操作符，則使用之，否則繼續下一 步。
b.如果任何輸入參數是一種域類型，對所有後續步驟都把它當做是該 域的基類型。這確保在做有歧義的操作符解析時，域的舉止像它們 的基類型。
c.遍曆所有候選操作符，保留那些在輸入類型上的匹配最準確的。如果沒有一個操作符能準確匹配，則保留所有候選。如果隻剩下一個候選操作符，則使用之，否則繼續下一步。
d.遍曆所有候選操作符，保留那些在最多個需要類型轉換的位置上接受首選類型（屬於輸入數據類型的類型分類）的操作符。如果沒有接受首選類型的操作符，則保留所有候選。如果隻剩下一個候選操作符，則使用之， 否則繼續下一步。
e.如果有任何輸入參數是unknown類型，檢查被剩餘候選操作符在那些參數位置上接受的類型分類。 在每一個位置，如果任何候選接受該分類，則選擇string分類（這種對字符串的偏愛是合適的， 因為未知類型的文本確實像字符串)。否則，如果所有剩下的候選操作符都接受相同的類型 分類，則選擇該分類；否則拋出一個錯誤（因為在沒有更多線索的條件下無法作出正確 的推斷）。現在拋棄不接受選定的類型分類的候選操作符。然後，如果任意候選操作符接受那個分類中的首選類型， 則拋棄那些在該參數位置接受非首選類型的候選操作符。如果沒有候選操作符能通過這些測試則保留全部候選者。如果隻剩下一個候選者，則使用之；否則繼續下一步。
f.如果既有unknown參數也有已知類型的參數，並且所有已知類型參數具有相同的類型，則假定該unknown參數也是那種類型的，並且檢查哪些候選操作符可以在該unknown參數的位置上接受那個類型。如果正好有一個候選者通過了這個測試，則使用之；否則失敗。
下麵是一些例子。
Example 10-1. 階乘操作符類型決定
在標準目錄中隻有一個被定義的階乘操作符（後綴!），它接受一個類型為bigint的參數。在下麵這個查詢表達式中，掃描器會為該參數分配一個初始類型integer：
SELECT 40 ! AS "40 factorial";
40 factorial
--------------------------------------------------
815915283247897734345611269596115894272000000000
(1 row)
因此，解析器在操作數上做了一個類型轉換，該查詢等價於：
SELECT CAST(40 AS bigint) ! AS "40 factorial";
Example 10-2. 字符串連接操作符類型決定
一個類字符串的語法被用來處理字符串類型和處理複雜的擴展類型。未指定類型的字符串與可能的候選操作符匹配。
一個未指定參數的例子：
SELECT text 'abc' || 'def' AS "text and unknown";
text and unknown
------------------
abcdef
(1 row)
在這種情況下，解析器查看是否有一個操作符的兩個參數都使用text。既然有，那麼它假設第二個參數應被解釋為text類型。
下麵是兩個未指定類型的值的連接：
SELECT 'abc' || 'def' AS "unspecified";
unspecified
-------------
abcdef
(1 row)
在這種情況下，沒有對於使用哪種類型的初始提示，因為在查詢中沒有指定類型。 因此，解析器查找所有的候選操作符並找到候選者同時接受字符串分類和位串分類的輸入。 因為字符串分類在可用時是首選的，該分類會被選中，並且接下來字符串的首選類型（text）會被用作解決未知類型文字的指定類型。
Example 10-3. 絕對值與否定操作符類型決定
PostgreSQL操作符目錄中有幾個對於前綴操作符@的條目， 這些都現實了針對各種數字數據類型的絕對值操作。其中之一用於float8類型，它是在數字分類中的首選類型。 因此，PostgreSQL將在遇到一個unknown輸入時使用它：
SELECT @ '-4.5' AS "abs";
abs
-----
4.5
(1 row)
在這裏，係統
```在應用所選操作符之前已經隱式地解決了將未知類型文字作為float8類型。 我們可以驗證我們使用的是float8而不是別的類型：
```java
SELECT @ '-4.5e500' AS "abs";
ERROR: "-4.5e500" is out of range for type double precision
另一方麵，前綴符~（按位取反）隻為整數數據類型定義，而沒有為float8定義。因此，如果我們嚐試一個與使用~類似的情況，我們會得到：
SELECT ~ '20' AS "negation";
ERROR: operator is not unique: ~ "unknown"
HINT: Could not choose a best candidate operator. You might need to add
explicit type casts.
這是因為係統不能決定在幾個可能的~符號中應該選擇哪一個。我們可以用一個顯式造型來幫助它：
SELECT ~ CAST('20' AS int8) AS "negation";
negation
----------
-21
(1 row)
Example 10-4. 數組包含操作符類型決定
這裏是另一個決定帶有一個已知和一個未知輸入的操作符的例子：
SELECT array[1,2] <@ '{1,2,3}' as "is subset";
is subset
-----------
t
(1 row)
PostgreSQL操作符目錄有一些條目用於中綴操作符<@，但是僅有的兩個可以在左手邊接受一個整數數組的是數組包含（anyarray <@ anyarray）和範圍包含（anyelement <@ anyrange）。因為這些多態偽類型（見Section 8.20）中沒有一個被認為是首選的，解析器不能以此為基礎來解決歧義。不過，step 3.f告訴它假定位置類型的文字和其他輸入的類型相同，即整數數組。現在這兩個操作符中隻有一個可以匹配，因此數組包含被選擇（如果選擇範圍包含，我們將得到一個錯誤，因為該字符串沒有成為一個範圍文字的正確格式）。
Example 10-5. 域類型上的自定義操作符
用戶有時會嚐試聲明隻適用於一種域類型的操作符。這是可能的， 但是遠非它看起來那麼有用，因為操作符解析規則被設計為選擇 適用於域的基類型的操作符。考慮這個例子：
CREATE DOMAIN mytext AS text CHECK(...);
CREATE FUNCTION mytext_eq_text (mytext, text) RETURNS boolean AS ...;
CREATE OPERATOR = (procedure=mytext_eq_text, leftarg=mytext, rightarg=text);
CREATE TABLE mytable (val mytext);
SELECT * FROM mytable WHERE val = 'foo';
這個查詢將不會使用自定義操作符。解析器將首先看看是否有一個 mytext = mytext操作符（ step 2.a），當然這裏沒有； 然後它將會考慮該域的基類型text，並且看看是否有一 個text = text操作符（ step 2.b），這裏也沒有；因 此它會把unknown-類型文字解析為text 並使用text = text操作符。 讓自定義操作符能被使用的唯一方法是顯式地轉換改文字：
SELECT * FROM mytable WHERE val = text 'foo';
這樣根據準確匹配規則會立即找到 mytext = text操作符。如果 到達最佳匹配規則，它們會積極地排斥域類型上的操作符。如果它 們沒有，這樣一個操作符將創建太多歧義操作符失敗，因為轉換規 則總是認為一個域可以和它的基類型相互轉換，並且因此該域操作 符在所有與該基類型上的一個類似命名的操作符相同的情況中都被 認為可用。
被一個函數調用引用的特定函數使用下麵的過程來決定。
函數類型決定
1.從pg_proc係統目錄中選擇要被考慮的函數。 如果使用一個非模式限定的函數名稱，那麼函數被認為是那些在當前搜索路徑中可見並有匹配的名字和參數個數的函數（參見Section 5.8.3）。如果給出一個被限定的函數名，那麼隻考慮指定模式中的函數。
a.如果搜索路徑發現多個參數類型相同的函數，那麼隻考慮最早在搜索路徑中出現的那個。 不同參數類型的函數被平等對待，不受在搜索路徑中位置的影響。
b.如果使用一個VARIADIC數組參數聲明一個函數，並且調用不使用關鍵字VARIADIC， 那麼該函數就好像其數組參數被它的元素類型的一次或多次出現所替換，根據需要去匹配調用。 這樣的擴展之後，函數可能會有和非可變函數相同的參數類型。在這種情況下，在搜索路徑中出現比較早的函數將被使用，或者如果兩個函數在相同的模式中時首選非可變的那一個。
c.考慮使用有默認參數值的函數來匹配任何省略了零個或者多個可默認參數位置的調用。如果有超出一個的這種函數匹配一個調用，那麼使用最早出現在搜索路徑中的那個。如果同一個模式中在同一個非默認位置上有兩個或者更多這樣的函數（如果它們有 不同的默認參數設置，這是可能的），係統將不能確定去選擇哪一個，並且如果不能找到該調用更好的匹配，將會導致一個"有歧義的函數調用" 錯誤。
2.檢查一個函數正好接受輸入參數類型。如果存在一個（在所考慮的一組函數中隻能有一個準確匹配），則使用之（在該步驟中，涉及unknown的情況將永遠找不到一個匹配）。
3.如果沒有發現準確匹配，那麼查看函數調用是否作為一個特定的類型轉換請求出現。 如果函數調用僅有一個參數並且函數名和一些數據類型的（內部）名稱相同，那麼該情況將會發生。 並且，該函數參數必須是一個未知類型的文字，或者是一個可以被二進製強製轉換到命名數據類型的類型， 或者是一個可以通過應用其I/O函數被轉換為命名數據類型的類型（也就是，轉換是轉到標準字符串類型或者從標準字符串類型轉來）。當滿足這些條件的時候，函數調用被當做CAST聲明的一種形式來對待。 [1]
4.查找最佳匹配。
a.如果候選函數的輸入類型不匹配並且不能通過轉換（使用一個隱式轉換）達到匹配，則丟棄它。為了這個目的，unknown文字被假定可被轉換成任何東西。如果僅有一個候選項，則使用之；否則繼續下一步。
b.如果任何輸入參數是一種域類型，在所有後續步驟中都把它當做 該域的基類型。這確保在做有歧義的操作符解析時，域的舉止像它們 的基類型。
c.遍曆所有候選函數並保留那些最匹配輸入類型的。如果沒有準確匹配，則保留所有候選項。 如果僅有一個候選項，則使用之；否則繼續下一步。
d.遍曆所有候選函數並保留那些在最多要求類型轉換的位置上接受首選類型（屬於輸入數據類型的類型分類）的候選項。如果沒有接受首選類型的候選項，則保留所有候選項。如果僅有一個候選項，則使用之；否則繼續下一步。
e.如果任何輸入參數是unknown，那麼檢查那些被剩餘候選項在那些參數位置上接受的類型分類。在每一個位置上，如果任何候選項接受該分類則選擇string分類 （這個偏向於字符串是恰當的，因為一個未知類型文字看起來像字符）。 否則，如果所有剩餘的候選項接受相同的類型分類，那麼選擇那個分類； 否則將失敗，因為缺乏更多線索來推斷出正確的選擇。現在，丟棄不接受被選中類型分類的候選項。此外，如果任何候選項接受那個分類中的一個首選類型，則丟棄對該參數接受非首選類型的候選項。如果沒有候選項能通過這些測試，則保留所有候選項。如果隻剩下一個候選項，則使用之；否則繼續下一步。
f.如果既有unknown參數也有已知類型的參數，並且所有已知類型參數具有相同的類型，則假定該unknown參數也是那種類型的，並且檢查哪些候選函數可以在該unknown參數的位置上接受那個類型。如果正好有一個候選者通過了這個測試，則使用之；否則失敗。
注意，對於操作符和函數類型決定來說"最優匹配"規則是完全相同的。下麵是一些例子。
Example 10-6. 圓整函數參數類型決定
隻有一個帶有兩個參數的圓整函數； 它采用第一個參數類型為numeric和第二個參數類型為integer。這樣下麵的查詢自動將第一個類型為integer參數轉換為numeric：
SELECT round(4, 4);
round
--------
4.0000
(1 row)
該查詢實際上被解析器轉換為：
SELECT round(CAST (4 AS numeric), 4);
因為包含小數點的數字常數初始會被分配類型numeric，下麵的查詢將不需要類型轉換並因此可能會稍稍高效一些：
SELECT round(4.0, 4);
Example 10-7. 子串函數類型決定
有幾個substr函數，其中一個用於text和integer類型。如果使用一個未指定類型的字符常量調用，那麼係統選擇接受一個首選分類string（也就是text類型）的參數的候選函數。
SELECT substr('1234', 3);
substr
--------
34
(1 row)
如果字符串被聲明為類型varchar（如果它來自於一個表就會這樣），那麼解析器將嚐試轉換它為text：
SELECT substr(varchar '1234', 3);
substr
--------
34
(1 row)
解析器所作的轉換：
SELECT substr(CAST (varchar '1234' AS text), 3);
Note:
解析器從pg_cast目錄中知道text和varchar是二進製可兼容的， 意思是其中一個可以被傳遞給接受另一種類型的函數而不需要做任何物理轉換。因此，在這種情況下不會真正使用類型轉換調用。
並且，如果該函數使用一個integer類型的參數調用，那麼解析器將嚐試將它轉換為text：
SELECT substr(1234, 3);
ERROR: function substr(integer, integer) does not exist
HINT: No function matches the given name and argument types. You might need
to add explicit type casts.
由於integer類型沒有到text的一個隱式造型，這將不會工作。但是一次顯式造型則可以工作：
SELECT substr(CAST (1234 AS text), 3);
substr
--------
34
(1 row)
Notes
[1]
這一步的原因是在沒有一個實際的造型函數的情況下支持函數風格的造型聲明。如果有一個造型函數，它被按慣例以其輸出類型命名，並且不需要有特殊情況。
將被插入到一個表的值會按照下列步驟被轉換到目標列的數據類型。
值存儲類型轉換
1.檢查一個與目標的準確匹配。
2.否則，嚐試轉換表達式為目標類型。如果在兩種類型之間的一個 賦值造型已經被注冊在pg_cast 目錄（見CREATE CAST）中， 這是可能的。或者，如果該表達式是一個未知類型的文字， 則該文字串的內容將被提供給目標類型的輸入轉換例程。
3.檢查是否有一個用於目標類型的尺寸調整造型。尺寸調整造型是一個從該類型到其自身的造型。 如果在pg_cast目錄中找到一個，那麼把表達式存儲到目標列中之前把它應用到表達式。這樣一個造型的實現函數總是采用一個額外的integer類型的參數，它接收目標列的atttypmod值（通常是它被聲明的長度，盡管對於不同數據類型atttypmod有不同的解釋），並且它可能采用第三個boolean參數來說明造型是顯式的還是隱式的。該造型函數負責應用任何長度相關的語義，例如尺寸檢查或截斷。
Example 10-8. character存儲類型轉換
對於一個聲明為character(20)的目標列，下麵的語句展示了被存儲的值如何被正確地調整尺寸：
CREATE TABLE vv (v character(20));
INSERT INTO vv SELECT 'abc' || 'def';
SELECT v, octet_length(v) FROM vv;
v | octet_length
----------------------+--------------
abcdef | 20
(1 row)
實際發生的事情是兩個未知文字被默認決定為text，允許||操作符被決定為text連接。 然後操作符的text結果被轉換成bpchar（"blank-padded char"空白填充字符，character數據類型的內部名稱）來匹配目標列類型（由於從text到bpchar的轉換是二進製強製的，這個轉換不會插入任何實際的函數調用）。最後，尺寸調整函數bpchar(bpchar, integer, boolean)被從係統目錄中找到並應用到操作符的結果和存儲的列長度上。這個類型相關的函數執行必要的長度檢查並增加填充的空間。
SQL UNION結構必須使可能不相似的類型匹配成為一個單一的結果集。該決定算法被獨立地應用到一個聯合查詢的每個輸出列。 INTERSECT和EXCEPT采用和UNION相同的方法來決定不相似的類型。CASE、ARRAY、VALUES、GREATEST和LEAST結構使用相同的算法來使它們的組成表達式匹配並選擇一種結果數據類型。
UNION、CASE和相關結構的類型決定
- 如果所有的輸入為相同類型，並且不是unknown，那麼就決定是該類型。
- 如果任何輸入是一種域類型，在所有後續步驟中都把它當做 該域的基類型。 [1]
- 如果所有的輸入為unknown類型，則決定為text（字符串分類的首選類型）類型。否則，unknown輸入被忽略。
- 如果非未知輸入不全是相同的類型分類，則失敗。
- 如果有的話，選擇第一個在其分類中作為首選類型的非未知輸入類型。
- 否則，選擇最後的非未知輸入類型，它允許所有在前麵的非未知輸入被隱式地轉換為它（總有這樣的一種類型，因為至少在列表中的第一個類型必須滿足這個條件）。
- 轉換所有的輸入為選定的類型。如果沒有一個從給定輸入到選定類型的轉換將會失敗。
下麵是一些例子。
Example 10-9. 聯合中未指定類型的類型決定
SELECT text 'a' AS "text" UNION SELECT 'b';
text
------
a
b
(2 rows)
這裏，未知類型文字'b'將被決定為類型text。
Example 10-10. 簡單聯合中的類型決定
SELECT 1.2 AS "numeric" UNION SELECT 1;
numeric
---------
1
1.2
(2 rows)
文字1.2是numeric類型，且integer值1可以被隱式地造型為numeric，因此使用numeric類型。
Example 10-11. 可換位聯合中的類型決定
SELECT 1 AS "real" UNION SELECT CAST('2.2' AS REAL);
real
------
1
2.2
(2 rows)
這裏，由於類型real被能被隱式地造型為integer，而integer可以被隱式地造型為real，聯合結果類型被決定為real。
Notes
[1]
多少有些類似於對待用於操作符和函數的域輸入的方式，這種行為允許 一種域類型能通過一個UNION或相似的結構保留下來， 隻要用戶小心地確保所有的輸入都是（顯式地或隱式地）準確類型。否 則會優先選擇該域的基類型。
最後更新：2017-08-18 12:02:24