Full Text PostgreSQL Search

Question

Now I will learn about full-text search in PostgreSQL 9.2.3. However, I have a problem. I ran 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 do not get the result - 0 rows are returned. Could you tell me why? According to the PostgreSQL documentation, it should work.

only "titl" and "bodi" as a query get the appropriate result. Why?

Answer 1

to_tsquery , only one argument uses the default text search configuration, which seems non-English in your case.

You can 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 the text configuration is, and SET default_text_search_config TO 'english' to change it to English.