Fulltext Postgres

Question

I created an index for full text search in postgresql.

CREATE INDEX pesquisa_idx 
ON chamado 
USING 
gin(to_tsvector('portuguese', coalesce(titulo,'') || coalesce(descricao,'')));

When I run this query:

SELECT * FROM chamado WHERE to_tsvector('portuguese', titulo) @@ 'ura'

It returned to me some rows.

But when my argument is in all uppercase, no rows are returned. For example:

SELECT * FROM chamado WHERE to_tsvector('portuguese', titulo) @@ 'URA'

When the argument is 'ura' I get a few lines; when the argument is 'URA' I do not get any rows.

Why does this happen?

Ivan Shagin · Accepted Answer · 2013-04-30 20:45:04Z

3

You get no matches in the second case since to_tsvector() lowercases all lexemes. Use to_tsquery() to build the query, it will take care of the case issues as well:

SELECT * FROM chamado WHERE to_tsvector('portuguese', titulo) @@ to_tsquery('portuguese', 'URA')

answered Apr 30, 2013 at 20:45

Ivan Shagin

4233 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Fulltext Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related