Full Text Search#

Postgres supports many full-text search options. Your options include:

• LIKE
• soundex, levenshtein distance, (double) metaphone

However, the most powerful is by far using tsvector with tsquery.

Supabase recommends using it for the full-text search.

Fuzzy Full Text Search#

We can, however, use it for fuzzy full-text search, even on multiple columns.

• to_tsvector encodes the column data
• to_tsquery encodes the search data to search the column data

I created a complex function like this:

	CREATE OR REPLACE FUNCTION search_posts(phrase TEXT)
RETURNS SETOF posts
LANGUAGE plpgsql AS
$$
BEGIN
RETURN QUERY
WITH search AS (
  SELECT to_tsquery(string_agg(lexeme || ':*', ' & ' order by positions)) AS query
  FROM unnest(to_tsvector(phrase))
)
SELECT posts.*
FROM posts, search
WHERE (posts.content @@ search.query);
END
$$;

You may need to do this in order to get complex tsvector function usage.

Other References:

• https://stackoverflow.com/questions/29649751/postgres-add-full-text-search-on-existing-varchar-column
• https://stackoverflow.com/questions/46122175/fulltext-search-combined-with-fuzzysearch-in-postgresql
• https://github.com/supabase/supabase/discussions/5435#discussioncomment-2172376
• https://www.postgresql.org/docs/current/datatype-textsearch.html#DATATYPE-TSQUERY

The EASY Supabase Way#

Thanks to postgREST under-the-hood, you can simply do this for AND and OR searches:

	// select all columns from posts where posts.content has 'phrase'
supabase.from('posts').select('*').textSearch('content', phrase);

// select all columns from posts where posts.content AND posts.title have 'phrase'
supabase.from('posts').select('*')
  .textSearch('content', phrase, { type: 'phrase' })
  .textSearch('title', phrase, { type: 'phrase' });

// select all columns from posts where posts.content OR posts.title have 'phrase'
supabase.from('search_posts').select('*')
  .or(`title.phfts.${phrase},content.phfts.${phrase}`);

// you can also change the language
.textSearch('title', phrase, { type: 'phrase', config: 'english' });

However, I would still suggest creating indexes for each one if possible:

	CREATE INDEX posts_title_index ON public.posts USING gin (to_tsvector('simple'::regconfig, title))

And that's it. There could be a whole website dedicated to tsvector alone, but this should save you some time.

J