Full text search on PostgreSQL

Question

Now I'm learning about full text search in PostgreSQL 9.2.3. However, I have a problem. I run this example:

CREATE TABLE messages (title text,body text,tsv tsvector);

CREATE TRIGGER tsvectorupdate 
BEFORE INSERT OR UPDATE ON messages FOR EACH ROW EXECUTE PROCEDURE
tsvector_update_trigger(tsv, 'pg_catalog.english', title, body);

INSERT INTO messages VALUES('title here', 'the body text is here');

Unfortunately, after:

SELECT title, body FROM messages WHERE tsv @@ to_tsquery('title & body')

I get no result - 0 rows returned. Could you tell me why? According to the documentation of PostgreSQL it should work.

only 'titl' and 'bodi' as a query get aprropriate result. Why?

Answer 1

to_tsquery with only one argument uses the default text search config that appears not to be english in your case.

You could use the form with an explicit text config to get the expected result:

SELECT title, body FROM messages
  WHERE tsv @@ to_tsquery('english', 'title & body')

Use SHOW default_text_search_config in SQL to see what is the text configuration in effect and SET default_text_search_config TO 'english' to change it to english.