Geek Answers Handbook

Cannot get correct results after full text search after publishing

I am developing a simple article site in Brazilian Portuguese. The search function is based on full-text search, but does not return the expected results.

I did this on postgresql. Here is a simplified table:

Artigos
-id
-title -- article title
-intro -- article introduction
-content -- article body
-publishdate -- date of launch
-artigosts -- this will work as our fts index.

After creating the table, I ran:

UPDATE artigos SET artigosts = 
setweight(to_tsvector('pg_catalog.portuguese', coalesce(title,'')), 'A') || 
setweight(to_tsvector('pg_catalog.portuguese', coalesce(intro,'')), 'B') ||
setweight(to_tsvector('pg_catalog.portuguese', coalesce(content,'')), 'C');

CREATE INDEX artigosts_idx ON artigos USING gist (artigosts);

CREATE TRIGGER artigosts_tg 
BEFORE INSERT OR UPDATE ON artigos 
FOR EACH ROW EXECUTE PROCEDURE 
  tsvector_update_trigger('artigosts', 'pg_catalog.portuguese', 'title', 'intro', 'content');

Yes, I intend to use simple weighting when searching. Created an index for acceleration, a trigger, so I can insert and update without worrying about altering the index, etc.

Well, in my understanding, everything is fine. But there are no results. A simple example.

Let's say I have "... banco de dados ... no banco ..." as one content of the article. When I do this:

SELECT title, intro, content FROM artigos WHERE plainto_tsquery('banco de dados') @@ artigosts;

. ts_vector "banc" "dad". , , .

- ?

+5
2

, , , . , , .

SELECT * FROM ts_debug('banco de dados');

, . "banco", "de" "dado". , , , 0 .

:

SELECT * FROM ts_debug('portuguese', 'banco de dados');

, , "banc" "dad". , , .

SELECT title, intro, content FROM artigos WHERE plainto_tsquery('portuguese', 'banco de dados') @@ artigosts;
+4

, . , ...

, tsvector.

SELECT to_tsvector('simple',unaccent('banco de dados áéóíúçãõ'))

, :

"'aeoiucao':4 'banco':1 'dados':3 'de':2"

:

CREATE EXTENSION unaccent;

... postgres. , tsquery

+1

More articles:


All Articles