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

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')

Collectives™ on Stack Overflow

Fulltext Postgres

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related