Alex Rivera | Logout

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

Asked 2013-01-25T14:18:02.867
11

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?

sql postgresql types casting full-text-search
Edit
Report

1 Answer

16

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.

answered 2013-01-25T15:09:28.383

Your Answer