Postgresql Error: Function To_Tsvector(Character Varying, Unknown) Does Not Exist

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

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

postgresql [42883] ERROR: function to_tsvector(unknown, unknown) does not exist

You can try to check, what types are used (I am using psql client`):

postgres=# \df to_tsvector
                             List of functions
┌────────────┬─────────────┬──────────────────┬─────────────────────┬──────┐
│   Schema   │    Name     │ Result data type │ Argument data types │ Type │
╞════════════╪═════════════╪══════════════════╪═════════════════════╪══════╡
│ pg_catalog │ to_tsvector │ tsvector         │ json                │ func │
│ pg_catalog │ to_tsvector │ tsvector         │ jsonb               │ func │
│ pg_catalog │ to_tsvector │ tsvector         │ regconfig, json     │ func │
│ pg_catalog │ to_tsvector │ tsvector         │ regconfig, jsonb    │ func │
│ pg_catalog │ to_tsvector │ tsvector         │ regconfig, text     │ func │
│ pg_catalog │ to_tsvector │ tsvector         │ text                │ func │
└────────────┴─────────────┴──────────────────┴─────────────────────┴──────┘
(6 rows)

There is not any variant for type character, character.

Your first query is working in my comp. Please, check, version of Postgres, that you use. Older (very old - years unsupported releases) Postgres has not this functionality

postgres=# SELECT to_tsvector('english', 'The quick brown fox jumped over the    lazy dog.');
┌───────────────────────────────────────────────────────┐
│                      to_tsvector                      │
╞═══════════════════════════════════════════════════════╡
│ 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2 │
└───────────────────────────────────────────────────────┘
(1 row)

When you want to use explicit types, you can use regconfig and text:

postgres=# SELECT to_tsvector('english'::regconfig, 
                   'The quick brown fox jumped over the lazy dog.'::text);
┌───────────────────────────────────────────────────────┐
│                      to_tsvector                      │
╞═══════════════════════════════════════════════════════╡
│ 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2 │
└───────────────────────────────────────────────────────┘
(1 row)

Creating GIN index throws function does not exist error

As content_vector is already a tsvector, just build the index on it. to_tsvector doesnt take a tsvector as it's second argument, not would it make sense to.

No function matches the given name and argument types

Your function has a couple of smallint parameters.

But in the call, you are using numeric literals that are presumed to be type integer.

A string literal or string constant ('123') is not typed immediately. It remains type "unknown" until assigned or cast explicitly.

However, a numeric literal or numeric constant is typed immediately. The manual:

A numeric constant that contains neither a decimal point nor an
exponent is initially presumed to be type integer if its value
fits in type integer (32 bits); otherwise it is presumed to be type
bigint if its value fits in type bigint (64 bits); otherwise it is
taken to be type numeric. Constants that contain decimal points and/or
exponents are always initially presumed to be type numeric.

Also see:

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

Solution

Add explicit casts for the smallint parameters or pass quoted (untyped) literals.

Demo

CREATE OR REPLACE FUNCTION f_typetest(smallint)
  RETURNS bool AS 'SELECT TRUE' LANGUAGE sql;

Incorrect call:

SELECT * FROM f_typetest(1);

Correct calls:

SELECT * FROM f_typetest('1');
SELECT * FROM f_typetest(smallint '1');
SELECT * FROM f_typetest(1::int2);
SELECT * FROM f_typetest('1'::int2);

db<>fiddle here

_{Old sqlfiddle.}

postgresql trigger on tsvector column get ERROR: column does not exist

Indeed. It should be new.textsearchable_index_col. The same for subsequent fields.

(Btw, there's little point in assigning tsv twice. Either use your first (corrected) statement, or your second. But not both, since the second overrides the first, and a tsv is expensive to compute.)

Testing full-text search with textacular and cucumber throws PG::Error: ERROR: function similarity(character varying, unknown) does not exist

For anyone else out there who runs into this issue, you can run bundle exec rake textacular:install_trigram all you like, but if you use rake to run cucumber or rspec, then db:test:prepare is invoked, and since trigram is not being installed through a migration, this has the effect of wiping it from the db when the schema is loaded!

# install trigram in test db
bundle exec rake textacular:install_trigram RAILS_ENV=test

# wipe trigram from db
rake cucumber
rake rspec

To stop this from happening, just run cucumber and rspec without rake!

# run cucumber and rspec without rake
cucumber
rpsec

What a horrible problem! 2 days to fix etc...

PostgreSQL 9.5 Coalesce

Looks like you have the parentheses in the wrong place.

to_tsvector takes at most 2 arguments, but the way you have it right now it is taking three.

This should fix it:

UPDATE recipes SET recipes_searchtext = 
setweight(to_tsvector('german',COALESCE(name)), 'A') ||
setweight(to_tsvector('german',COALESCE(description)), 'B') ||
setweight(to_tsvector('german',COALESCE(preparation)), 'C');

However, I don't see a difference between your queries. coalesce will return null if all its arguments are null. You can provide a default value if you want like so: coalesce(name, 'Nothing') which will return 'Nothing' if name is null

---

Related Topics