本節內容來源於客戶現場反饋的一個問題.
**Question**
建立了integer到text的cast後,為何執行字串連線"||"操作報錯?
```
testdb=# drop table if exists t_cast ;
DROP TABLE
testdb=# create table t_cast (id int);
CREATE TABLE
testdb=# insert into t_cast values(1),(2),(3);
INSERT 0 3
testdb=# create cast(integer as text) with inout as implicit;
CREATE CAST
testdb=# select id||'X' from t_cast;
psql: ERROR:  operator is not unique: integer || unknown
LINE 1: select id||'X' from t_cast;
                 ^
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.
```

**Answer**
PostgreSQL處理運算子時分為以下兩個步驟:
1.從系統目錄pg_operator中找出匹配該運算子和運算元的候選函式(資料行);
2.根據隱式轉換規則選擇合適的operator.

*原生PG*
透過分析和跟蹤程式碼,第一步,從pg_operator中選出的後續函式OID為374/2780
```
testdb=# select oid,oprname,oprleft::regtype,oprright::regtype 
testdb-# from pg_operator 
testdb-# where oid in (374,2780);
 oid  | oprname |   oprleft   | oprright 
------+---------+-------------+----------
  374 | ||      | anyelement  | anyarray
 2780 | ||      | anynonarray | text
(2 rows)
```
而'X'可視為text,最終PostgreSQL會選擇OID = 2780的operator,因此不會出錯.

*建立integer -> text轉換*
而建立了integer -> text的cast後,從pg_operator中選出的後續函式OID為654/2779/374/2780
```
testdb=# select oid,oprname,oprleft::regtype,oprright::regtype 
testdb-# from pg_operator 
testdb-# where oid in (654,2779,374,2780);
 oid  | oprname |   oprleft   |  oprright   
------+---------+-------------+-------------
  374 | ||      | anyelement  | anyarray
  654 | ||      | text        | text
 2779 | ||      | text        | anynonarray
 2780 | ||      | anynonarray | text
(4 rows)
```
因為integer可以轉換為text,PostgreSQL無法在2779和2780之間無法做出選擇,因此出現錯誤:Could not choose a best candidate operator.

**參考資料**
[PostgreSQL 原始碼解讀（209）- 隱式型別轉換(func_select_candidate) ](http://blog.itpub.net/6906/viewspace-2650560/)
[PostgreSQL 原始碼解讀（210）- 隱式型別轉換(func_match_argtypes) ](http://blog.itpub.net/6906/viewspace-2650700/)

PostgreSQL DBA(57) - Could not choose a best candidate operator

相關文章