# Supabase Fuzzy Full Text Search

![Supabase Fuzzy Full Text Search](https://cdn.hashnode.com/res/hashnode/imageupload/v1678071021643/d31e17ec-bbb8-4d3a-a4da-9cd6f09a548b.jpeg)

library\_books Supabase Fuzzy Full Text Search
----------------------------------------------

###### _4 min read_

[](https://code.build/u/86qJ3nXSvhBPUbmdaMwUeb/jdgamble555)

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

*   [LIKE](https://www.postgresql.org/docs/current/functions-matching.html)
*   [soundex, levenshtein distance, (double) metaphone](https://www.postgresql.org/docs/current/fuzzystrmatch.html)

However, the most powerful is by far using [tsvector with tsquery](https://www.postgresql.org/docs/current/datatype-textsearch.html).

Supabase recommends using it for the [full-text search](https://supabase.com/docs/guides/database/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/29649751/postgres-add-full-text-search-on-existing-varchar-column)
*   [https://stackoverflow.com/questions/46122175/fulltext-search-combined-with-fuzzysearch-in-postgresql](https://stackoverflow.com/questions/46122175/fulltext-search-combined-with-fuzzysearch-in-postgresql)
*   [https://github.com/supabase/supabase/discussions/5435#discussioncomment-2172376](https://github.com/supabase/supabase/discussions/5435#discussioncomment-2172376)
*   [https://www.postgresql.org/docs/current/datatype-textsearch.html#DATATYPE-TSQUERY](https://www.postgresql.org/docs/current/datatype-textsearch.html#DATATYPE-TSQUERY)

The EASY Supabase Way
---------------------

Thanks to [postgREST](https://postgrest.org/en/stable/) 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

  

* * *