This article showcases Full text search support in Postgres

Dataset

Execute these statements to setup the data DDL

Introducing tsvector and tsquery

tsvector

Postgres offers tsvector, a data type designed for efficient full-text search. It represents a document as a sorted list of distinct lexemes, which are normalized forms of words found in the text.

tsquery

Similarly, tsquery is a data type designed to represent text search queries within the context of full-text search. It stores a parsed and normalized representation of a search query, making it efficient for matching against tsvector documents.

Postgres documentation

Create the tsvector columns

We are going to create a generated column out of title and description fields in the original data set and assign weights to the lexemes. Here we are going to have matches in lexemes originating from title column higher ranked than description column.

alter table movies add column
 search_space tsvector generated always as 
 (
   setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
   setweight(to_tsvector('english', coalesce(description, '')), 'B')
 ) stored;
   
CREATE INDEX movies_idx ON movies USING gin(search_space);   

Querying

1. Lets say we want to find movies about vampires. This query will match singular, plural version of vampire in the search_space tsvector.

SELECT rank, title, genre, description FROM movies
WHERE
  search_space @@ to_tsquery('vampires');

2. OR search. This query searches for movies with words revenge or justice in title or description columns

SELECT rank, title, genre, description FROM movies
WHERE
  search_space @@ websearch_to_tsquery('revenge or justice');   
  

!! This is just a gentle introduction, clone this book and explore and try out more.