Supabase Fuzzy Full Text Search
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.
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:
TypeScript
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:
TypeScript
// 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:
SQL
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
Comments
Share a thought or join the conversation.
Sign In to comment or reply.