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

Question

I am new to postgresql and am attempting to use the full text search to_tsvector however I am running into an error.

SQL and error

SELECT to_tsvector('english', 'The quick brown fox jumped over the lazy dog.');

[42883] ERROR: function to_tsvector("unknown", "unknown") does not exist Hint: No function matches the given name and argument types. You may need to add explicit type casts.

SQL and error for different attempt

SELECT to_tsvector('english'::character, 'The quick brown fox jumped over the lazy dog.'::character);

[42883] ERROR: function to_tsvector(character, character) does not exist Hint: No function matches the given name and argument types. You may need to add explicit type casts.

This is frustrating because this feels like the 'hello world' of getting to_tsvector working however I cannot even get this to return. I am using DataGrip 2020.2 with Postgres, but am not sure how to see which version of postgres I am using (i think it is a newer version). Is there a clear mistake in my code above?

Answer 1

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)