本节内容来源于客户现场反馈的一个问题. **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/)