{"id":477,"date":"2022-08-30T15:02:00","date_gmt":"2022-08-30T15:02:00","guid":{"rendered":"https:\/\/unknownerror.org\/index.php\/2013\/11\/09\/postgresql-error-function-to_tsvectorcharacter-varying-unknown-does-not-exist-collection-of-common-programming-errors\/"},"modified":"2022-08-30T15:02:00","modified_gmt":"2022-08-30T15:02:00","slug":"postgresql-error-function-to_tsvectorcharacter-varying-unknown-does-not-exist-collection-of-common-programming-errors","status":"publish","type":"post","link":"https:\/\/unknownerror.org\/index.php\/2022\/08\/30\/postgresql-error-function-to_tsvectorcharacter-varying-unknown-does-not-exist-collection-of-common-programming-errors\/","title":{"rendered":"PostgreSQL ERROR: function to_tsvector(character varying, unknown) does not exist-Collection of common programming errors"},"content":{"rendered":"

The function signature cannot be guessed with the explicit type varchar<\/code>. Realize that an untyped string literal<\/strong> is not the same thing as parameter of type varchar<\/code>.<\/p>\n

Use instead:<\/p>\n

SELECT language, to_tsvector(language::regconfig, 'hello world') FROM languages;\n<\/code><\/pre>\n
Why?<\/h3>\n
The first argument of the 2-argument form is of type regconfig<\/code>, not text<\/code> or varchar<\/code>:<\/p>\n
SELECT proname, pg_get_function_arguments(oid)\nFROM   pg_catalog.pg_proc\nWHERE  proname = 'to_tsvector'\n\n   proname   | pg_get_function_arguments\n-------------+---------------------------\n to_tsvector | text\n to_tsvector | regconfig, text\n<\/code><\/pre>\n
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.<\/p>\n
However, if you use an explicit type<\/em> (like you do in your second example: varchar<\/code>) Postgres cannot find an implicit conversion from varchar<\/code> to regconfig<\/code> and does not consider the function you are looking for. You are telling Postgres: “Look no further, I want the function with a varchar<\/code> argument (or one that is only an implicit cast away)!”<\/p>\n
I quote from the chapter Type Conversion – Functions<\/strong> in the manual:<\/p>\n
\n
unknown literals are assumed to be convertible to anything for this purpose.<\/p>\n<\/blockquote>\n
The registered casts for varchar<\/code>, ordered by proximity:<\/p>\n
SELECT castsource::regtype, casttarget::regtype, castcontext\nFROM   pg_catalog.pg_cast\nWHERE  castsource = 'varchar'::regtype\nORDER BY castsource, castcontext = 'i' DESC, castcontext\n\n    castsource     |    casttarget     | castcontext\n-------------------+-------------------+-------------\n character varying | regclass          | i\n character varying | name              | i\n character varying | text              | i\n character varying | character         | i\n character varying | character varying | i\n character varying | \"char\"            | a\n character varying | xml               | e\n<\/code><\/pre>\n
Postgres would find a function where the signature can be reached with an implicit cast<\/em>.
\nExplanation for castcontext<\/code>:<\/p>\n
\n
castcontext char
\nIndicates 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.<\/p>\n<\/blockquote>\n
Read more about the three different types of assignment<\/em> in the chapter CREATE CAST.<\/p>\n
Originally posted 2013-11-09 19:44:39. <\/small><\/p>","protected":false},"excerpt":{"rendered":"
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) […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-477","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/477","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/comments?post=477"}],"version-history":[{"count":0,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/477\/revisions"}],"wp:attachment":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/media?parent=477"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/categories?post=477"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/tags?post=477"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}