Creating a Full Text Search Engine in PostgreSQL, 2022

The Code and data

createdb scifi psql scifi < questions.sql
--add the search index
alter table questions
add search tsvector
generated always as (
setweight(to_tsvector('simple',tags), 'A') || ' ' ||
setweight(to_tsvector('english',title), 'B') || ' ' ||
setweight(to_tsvector('english',body), 'C') :: tsvector

) stored;

-- add the index
create index idx_search on questions using GIN(search);

-- the search query
select title, body,
ts_rank(search, websearch_to_tsquery('english','vader tie fighter star-wars')) +
ts_rank(search, websearch_to_tsquery('simple','vader tie fighter star-wars')) as rank
from questions
where search @@ websearch_to_tsquery('english','vader tie fighter star-wars')
or search @@ websearch_to_tsquery('simple','vader tie fighter star-wars')
order by rank desc;

-- turning it into a function
create or replace function search_questions(term text)
returns table(
id int,
title text,
body text,
rank real
)
as
$$

select id, title, body,
ts_rank(search, websearch_to_tsquery('english',term)) +
ts_rank(search, websearch_to_tsquery('simple',term)) as rank
from questions
where search @@ websearch_to_tsquery('english',term)
or search @@ websearch_to_tsquery('simple',term)
order by rank desc;

$$ language SQL;

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Rob Conery

Rob Conery

Author of The Imposter’s Handbook, founder of bigmachine.io, Cofounder of tekpub.com, creator of This Developer's Life, creator of lots of open source stuff.