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?
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.
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 (usingCAST
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.
You could define your language column to be of type regconfig which would make your query a bit less verbose i.e.:
CREATE TABLE languages(language regconfig NOT NULL DEFAULT 'english'::regconfig)
I have set english as default above, but that's not required. Afterwards your original query
SELECT language, to_tsvector(language, 'hello world') FROM languages;
would work just fine.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With