Fantastic video. This is a masterclass. I am pretty new to PostgreSQL (I have experience of other SQL engines), I just hit the jackpot with your video. Keep doing videos like this!
hi sir, i'm having a probem with: alter table book add column fts_doc tsvector generated always as ( setweight(to_tsvector('english', title), 'A') || setweight(to_tsvector('simple', description), 'A') ) stored; =>> And the fts_doc result is: "''educational':6A 'ending':5A 'happy':4A 'motivation':3A 'strong':2A" but i think it should be "'educ':6A 'end':5A 'happi':4A'motiv':3A 'strong':2A" => This result comes from: select setweight(to_tsvector('Be Strong, Motivation, Happy Ending, Educational'), 'A') Why is it? Is there anything wrong that makes the diffrence?
in your fts_doc column part "to_tsvector('simple', description)" uses "simple" configuration that leaves the words as is, and just tokenizes description. Try using to_tsvector('english', description) instead.
In your java spring app example, the search-word is "albert -einstein life" and the result does not match the whole search-word (without the word " -einstein"). Can you please answer why the app still gives back search result? I am having problems with search-word from users that does not have in the tsvector-column (in this case, my app will show zero result). Thank you!
is your question why search: "albert -einstein life" on the beginning of tutorial gives results? Why do I get results Albert + life, but also only Albert and only life?
Thanks for your reply. Yes, that is my question, my Postgres FTS only gives results if all the search words are in ts_vector. What i want is to still recieve results even if there are some missing keywords like your example (2/3 searchword not 3/3). Thanks.
how are you searching on ts_vector column / converting to ts_query? In my case I'm using "document@@ websearch_to_tsquery(:q)". I'm using postgres build in function "websearch_to_tsquery" to convert user input to ts_query. Have a look at repository in video description for query example - FtsDocumentRepository class.
Amazing! Thank you so much for making this video 🙏
Fantastic video. This is a masterclass.
I am pretty new to PostgreSQL (I have experience of other SQL engines), I just hit the jackpot with your video.
Keep doing videos like this!
glad to be of help! I'll do more db and sql related videos
@@kanezi Nice to read your comment. Maybe special/unknown or barely known/underrated features of Postgresql
fantastic video! It will be nice to see a practical project built with this.. like searching pdf content or a library.. etc
If I could give you more than one of both thumbs ups and github stars, I would. Epic.
much appreciated!!!
Man, this is a treasure! please keep doing more advanced stuff like this!
thanks, I'll try! I'm also open for topic suggestions...
Such an in-depth explanation, very informative, keep it up, sir!
Thanks for such insightful content. Real enterprise grade stuff. Gratitudes!!!
Glad you find it helpful!
Your videos are great! Thank you very much!
great content, thanks for your effort.
Thanks man!
hi sir, i'm having a probem with:
alter table book
add column fts_doc tsvector
generated always as (
setweight(to_tsvector('english', title), 'A')
|| setweight(to_tsvector('simple', description), 'A')
)
stored;
=>> And the fts_doc result is: "''educational':6A 'ending':5A 'happy':4A 'motivation':3A 'strong':2A"
but i think it should be "'educ':6A 'end':5A 'happi':4A'motiv':3A 'strong':2A" => This result comes from:
select setweight(to_tsvector('Be Strong, Motivation, Happy Ending, Educational'), 'A')
Why is it? Is there anything wrong that makes the diffrence?
in your fts_doc column part "to_tsvector('simple', description)" uses "simple" configuration that leaves the words as is, and just tokenizes description. Try using to_tsvector('english', description) instead.
what happens if you look up "of" or "off"?
In your java spring app example, the search-word is "albert -einstein life" and the result does not match the whole search-word (without the word " -einstein"). Can you please answer why the app still gives back search result? I am having problems with search-word from users that does not have in the tsvector-column (in this case, my app will show zero result). Thank you!
is your question why search: "albert -einstein life" on the beginning of tutorial gives results? Why do I get results Albert + life, but also only Albert and only life?
Thanks for your reply. Yes, that is my question, my Postgres FTS only gives results if all the search words are in ts_vector. What i want is to still recieve results even if there are some missing keywords like your example (2/3 searchword not 3/3). Thanks.
how are you searching on ts_vector column / converting to ts_query? In my case I'm using "document@@ websearch_to_tsquery(:q)". I'm using postgres build in function "websearch_to_tsquery" to convert user input to ts_query. Have a look at repository in video description for query example - FtsDocumentRepository class.