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

The content of this section comes from a question from customer site feedback.

Question

After creating a cast from integer to text, why does the string concatenation "| |" report an error?

Testdb=# drop table if exists tonal castings; DROP TABLEtestdb=# create table t_cast (id int); CREATE TABLEtestdb=# insert into t_cast values (1), (2), (3); INSERT 0 3testdb=# create cast (integer as text) with inout as implicit;CREATE CASTtestdb=# select id | |'X' from tincture castcast` psql: ERROR: operator is not unique: integer | | unknownLINE 1: select id |'X' from tcastcastings; ^ HINT: Could not choose a best candidate operator. You might need to add explicit type casts.

Answer

PostgreSQL processes operators in the following two steps:

1. Find the candidate function (data row) that matches the operator and Operand from the system directory pg_operator

two。 Choose the appropriate operator according to the implicit transformation rules.

Native PG

By analyzing and tracking the code, in the first step, the subsequent function OID selected from pg_operator is 374Uniq2780.

Testdb=# select oid,oprname,oprleft::regtype,oprright::regtype testdb-# from pg_operator testdb-# where oid in (374jing2780); oid | oprname | oprleft | oprright-+-374 | anyelement | anyarray 2780 | anynonarray | text (2 rows)

And'X 'can be regarded as text, and eventually PostgreSQL will choose operator with OID = 2780, so there is no error.

Create an integer-> text transformation

After the cast of integer-> text is created, the subsequent function OID selected from pg_operator is 654max 2779max 374max 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) |

Because integer can be converted to text,PostgreSQL and cannot choose between 2779 and 2780, an error occurs: Could not choose a best candidate operator.

references

PostgreSQL Source Code interpretation (209)-implicit Type conversion (func_select_candidate)

PostgreSQL Source Code interpretation (210)-implicit Type conversion (func_match_argtypes)