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 varchar.

Use instead:

SELECT language, to_tsvector(language::regconfig, 'hello world') FROM languages;

Why?

The first argument of the 2-argument form is of type regconfig, not text or varchar:

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 varchar to 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.
Explanation for castcontext:

castcontext char
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.