sql - PostgreSQL ERROR: function to_tsvector(character varying, unknown) does not exist

Question

This psql session snippet should be self-explanatory:

psql (9.1.7)
Type "help" for help.
=> CREATE TABLE languages(language VARCHAR NOT NULL);
CREATE TABLE
=> INSERT INTO languages VALUES ('english'),('french'),('turkish');
INSERT 0 3
=> SELECT language, to_tsvector('english', 'hello world') FROM languages;
 language|     to_tsvector     
---------+---------------------
 english | 'hello':1 'world':2
 french  | 'hello':1 'world':2
 turkish | 'hello':1 'world':2
(3 rows)

=> SELECT language, to_tsvector(language, 'hello world') FROM languages;
ERROR:  function to_tsvector(character varying, unknown) does not exist
LINE 1: select language, to_tsvector(language, 'hello world')...
                         ^
HINT:  No function matches the given name and argument types.  
You might need to add explicit type casts.

The problem is that Postgres function to_tsvector doesn't like varchar field type but this call should be perfectly correct according to the documentation?

Answer 1

Use an explicit type cast:

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

Or change the column languages.language to type regconfig. See @Swav's answer.

Why?

Postgres allows function overloading. Function signatures are defined by their (optionally schema-qualified) name plus (the list of) input parameter type(s). The 2-parameter form of to_tsvector() expects type regconfig as first parameter:

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             -- you are here

If no existing function matches exactly, the rules of Function Type Resolution decide the best match - if any. This is successful for to_tsvector('english', 'hello world'), with 'english' being an untyped string literal. But fails with a parameter typed varchar, because there is no registered implicit cast from varchar to regconfig. The manual:

Discard candidate functions for which the input types do not match and cannot be converted (using an implicit conversion) to match. unknown literals are assumed to be convertible to anything for this purpose.

Bold emphasis mine.
The registered casts for regconfig:

SELECT castsource::regtype, casttarget::regtype, castcontext
FROM   pg_catalog.pg_cast
WHERE  casttarget = 'regconfig'::regtype;

 castsource | casttarget | castcontext
------------+------------+-------------
 oid        | regconfig  | i
 bigint     | regconfig  | i
 smallint   | regconfig  | i
 integer    | regconfig  | i

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.