Search by means of sql?

Question

PostgreSQL

It is necessary to organize a search for some information in the database.
Now used ilikefor such things. But it just looks for occurrences in the string and returns only those that have it.

I recently learned about full-text search . Looks cool, discards endings, searches for sentences, you can get rank.

But I ran into the problem that here it is necessary to know the exact beginning of the word, otherwise the search stops working.

In general, there is a strange thing:

-- TRUE
select to_tsvector('russian', 'съешь ещё этих мягких французских булок, да выпей чаю') @@ plainto_tsquery('russian', 'чай')

-- FALSE
select to_tsvector('russian', 'съешь ещё этих мягких французских булок, да выпей чаю') @@ plainto_tsquery('russian', 'булка')

-- FALSE
select to_tsvector('russian', 'съешь ещё этих мягких французских булок, да выпей чаю') @@ plainto_tsquery('russian', 'франц')

What is the best way to arrange a search using postgres itself (without using separate search engines like elasticSearch, etc.)

Answer 1

plainto_tsquery('russian', 'french*')
in general, study the docks, opportunities will open

Answer 2

Use trigrams, they can be used to make an inaccurate search. Here is an article on habré