PostgreSQL ERROR: function to_tsvector(character varying, unknown) does not exist-Collection of common programming errors
The function signature cannot be guessed with the explicit type
varchar. Realize that an untyped string literal is not the same thing as parameter of type
SELECT language, to_tsvector(language::regconfig, 'hello world') FROM languages;
The first argument of the 2-argument form is of type
SELECT proname, pg_get_function_arguments(oid) FROM pg_catalog.pg_proc WHERE proname = 'to_tsvector' proname | pg_get_function_arguments -------------+--------------------------- to_tsvector | text to_tsvector | regconfig, text
As long as you don’t use an explicit type, Postgres will consider all types a string literal can be cast to for a matching function signature.
However, if you use an explicit type (like you do in your second example:
varchar) Postgres cannot find an implicit conversion from
regconfig and does not consider the function you are looking for. You are telling Postgres: “Look no further, I want the function with a
varchar argument (or one that is only an implicit cast away)!”
I quote from the chapter Type Conversion – Functions in the manual:
unknown literals are assumed to be convertible to anything for this purpose.
The registered casts for
varchar, ordered by proximity:
SELECT castsource::regtype, casttarget::regtype, castcontext FROM pg_catalog.pg_cast WHERE castsource = 'varchar'::regtype ORDER BY castsource, castcontext = 'i' DESC, castcontext castsource | casttarget | castcontext -------------------+-------------------+------------- character varying | regclass | i character varying | name | i character varying | text | i character varying | character | i character varying | character varying | i character varying | "char" | a character varying | xml | e
Postgres would find a function where the signature can be reached with an implicit cast.
Indicates what contexts the cast can be invoked in. e means only as an explicit cast (using CAST or :: syntax). a means implicitly in assignment to a target column, as well as explicitly. i means implicitly in expressions, as well as the other cases.
Read more about the three different types of assignment in the chapter CREATE CAST.
Originally posted 2013-11-09 19:44:39.