Home
>
Supabase Fuzzy Full Text Search

Supabase Fuzzy Full Text Search

3 min read
Jonathan Gamble

jdgamble555 on Sunday, October 2, 2022 (last modified on Sunday, December 17, 2023)

Full Text Search

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

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

Supabase recommends using it for the 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:

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


Related Posts

© 2024 Code.Build