PostgreSQL與Oracle在資料比較上存在差異,本節簡單介紹OPostgreSQL的資料型別轉換規則(第三部分).

### 一、Value Storage
插入到資料表的值按如下步驟轉換為目標列資料型別.
**Value Storage Type Conversion**
1.檢查是否與目標完全匹配
2.如不能完全匹配,則轉換表示式為目標型別.如果在pg_cast系統目錄中存在兩種型別的轉換資訊,那麼這是可以做到的.否則,如果表示式為unknown型別,literal string的內容會反饋給輸入轉換例程,用於轉換為目標型別.
3.檢查目標型別是否有sizing轉換.sizing cast是從該型別到自身的一種轉換.如在pg_cast找到相應的cast,在儲存到目標列前在該表示式上應用該轉換.實現函式通常需要一個額外類為integer的引數,用於接收目標列的atttypmod屬性值(該值通常用於表示長度),同時需要第3個型別為boolean的引數用於確定cast是顯式還是隱式轉換.轉換函式有責任執行所有與長度相關的語義如大小檢查或截斷.

下面是一些例子:
*character 字元型別轉換*
```
testdb=# CREATE TABLE vv (v character(20));
CREATE TABLE
testdb=# INSERT INTO vv SELECT 'abc' || 'def';
INSERT 0 1
testdb=# SELECT v, octet_length(v) FROM vv;
          v           | octet_length 
----------------------+--------------
 abcdef               |           20
(1 row)
```
'abc'和'def'這兩者型別被視為unknown,轉換為text型別執行連線操作,||操作的結果為text,該結果轉換為bpchar(blank-padded char,character的內部名稱)用以匹配目標列型別.

檢視pg_cast系統目錄
```
testdb=# select oid,typname from pg_type where typname in ('text','bpchar','char');
 oid  | typname 
------+---------
   18 | char
   25 | text
 1042 | bpchar
(3 rows)

testdb=# select * from pg_cast where castsource=25;
  oid  | castsource | casttarget | castfunc | castcontext | castmethod 
-------+------------+------------+----------+-------------+------------
 11381 |         25 |       2205 |     1079 | i           | f
 11397 |         25 |       1042 |        0 | i           | b
 11398 |         25 |       1043 |        0 | i           | b
 11409 |         25 |         18 |      944 | a           | f
 11412 |         25 |         19 |      407 | i           | f
 11466 |         25 |        142 |     2896 | e           | f
(6 rows)
```
轉換函式
```
testdb=# select oid,proname,prorettype,proargtypes,prosrc from pg_proc where oid in (0,407,944,1079,2896);
 oid  | proname  | prorettype | proargtypes |    prosrc     
------+----------+------------+-------------+---------------
  407 | name     |         19 | 25          | text_name
  944 | char     |         18 | 25          | text_char
 1079 | regclass |       2205 | 25          | text_regclass
 2896 | xml      |        142 | 25          | texttoxml
(4 rows)
```

### 二、UNION, CASE, and related constructs
UNION(INTERSECT/EXCEPT)必須匹配可能的不太相同的型別以成形成一個單獨的結果集.該解析演算法分別應用於union查詢的每一個輸出列.CASE, ARRAY, VALUES, GREATEST 和 LEAST使用相同的演算法匹配表示式並選擇結果資料型別.
**Type Resolution for UNION, CASE, and Related Constructs**
1.如果所有輸入是同一種型別,而且不是unknown,則解析為該型別
2.如果所有輸入為同一個domain型別,則後續步驟的處理的時候把型別視為domain的基型別
3.如果所有輸入型別為unknown,則解析為text(字串的首選型別).否則,unknown型別的輸入接下來會被忽略
4.如果非unknown型別不是同一個型別,則失敗
5.選擇第一個非unknown型別的首選型別
6.否則,選擇允許所有優先非unknown輸入可被隱私轉換的最後一個非unknown輸入型別
7.轉換所有輸入為選擇的型別,如存在不能轉換的型別,則失敗

下面是一些例子:
*Simple Union*
```
testdb=# SELECT 1.2 AS "numeric" UNION SELECT 1;
 numeric 
---------
       1
     1.2
(2 rows)
```
1.2明確為numeric型別,integer 1可隱式轉換為numeric,因此使用numeric型別

*Transposed Union*
```
testdb=# SELECT 1 AS "real" UNION SELECT CAST('2.2' AS REAL);
 real 
------
    1
  2.2
(2 rows)

```
實數不能隱式轉換為整數,但整數可隱式轉換為real,因此1會被轉換為real,union結果型別為real.

*Nested Union*
```
testdb=# SELECT NULL UNION SELECT NULL UNION SELECT 1;
psql: ERROR:  UNION types text and integer cannot be matched
LINE 1: SELECT NULL UNION SELECT NULL UNION SELECT 1;

```
PG會把多個Unions視為成對的操作,該SQL可被視為"(SELECT NULL UNION SELECT NULL) UNION SELECT 1".按照上面的規則,內部union會解析為text型別,外部union的輸入為text和integer型別,導致上述錯誤.

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

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

相關文章