Created
December 5, 2019 17:36
-
-
Save adrienshen/e209aa71f10c6ae84b1895d9a914c2f0 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- search vectors column and create index | |
alter table movie_scripts add "document_vectors" tsvector; | |
create index idx_fts_doc_vector on movie_scripts using gin(document_vectors); | |
-- update the search vectors column with script | |
update | |
movie_scripts | |
set | |
document_vectors = (to_tsvector('english', names) || to_tsvector('english', script)); | |
-- basic querying the scripts based on relevancy | |
select | |
links | |
, names | |
, ts_headline('english', script, websearch_to_tsquery('english', 'Days of Summer')) | |
, ts_rank_cd(document_vectors, query) as relevance | |
from movie_scripts, websearch_to_tsquery('english', 'Days of Summer') query | |
where document_vectors @@ query | |
limit 5; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment