full text search

18 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

to_tsvector() converts data to searchable tokens

to_tsvector() stands for 'to text search vector' and converts your data into searchable tokens. For example, to_tsvector('green eggs and ham') returns 'egg':2 'green':1 'ham':4. These tokens are collectively called a 'document' which Postgres can use for comparisons.

to_tsquery() converts query strings into tokens

to_tsquery() stands for 'to text search query' and converts a query string into tokens to match. This conversion is important for 'fuzzy matching' on keywords, so if a user searches for 'eggs' and a column has 'egg', it will still return a match.

Postgres tsquery functions: to_tsquery, plainto_tsquery, phraseto_tsquery, websearch_to_tsquery

Postgres provides four functions to create tsquery objects: to_tsquery() requires manual specification of operators (&, |, !); plainto_tsquery() converts plain text to an AND query (plainto_tsquery('english', 'fat rats') → 'fat' & 'rat'); phraseto_tsquery() creates phrase queries (phraseto_tsquery('english', 'fat rats') → 'fat' <-> 'rat'); websearch_to_tsquery() supports web search syntax with quotes, 'or', and negation.

@@ is the full text search match operator

The @@ symbol is the 'match' symbol for Full Text Search. It returns any matches between a to_tsvector result and a to_tsquery result.

supabase-js textSearch() method for full text search

In supabase-js, use the textSearch() method to perform full text searches. Example: await supabase.from('books').select().textSearch('description', `'big'`). For web search syntax, pass type: 'websearch' in options: textSearch('description', 'green eggs', { type: 'websearch' }).

& operator for AND queries in full text search

Use the & symbol to search for documents containing ALL search terms. Example: to_tsquery('little & big') finds all books where description contains both 'little' AND 'big'.

| operator for OR queries in full text search

Use the | symbol to search for documents containing ANY of the search terms. Example: to_tsquery('little | big') finds all books where description contains either 'little' OR 'big'.

Partial search with :* in to_tsquery()

Use the :* syntax with to_tsquery() to search for substrings and prefix matches. Example: to_tsvector(title) @@ to_tsquery('Lit:*') finds any book titles beginning with 'Lit'.

Handling spaces in partial search queries

When searching for phrases or multiple words in partial search, concatenate words using a + as a placeholder for space. Example: search_books_by_title_prefix('Little+Puppy').

websearch_to_tsquery() for user-friendly search syntax

The websearch_to_tsquery() function provides intuitive search syntax similar to popular web search engines. It supports quoted phrases for exact matches, 'or' keyword for OR queries (case-insensitive), and dash (-) for negation. Example: websearch_to_tsquery('english', 'green eggs') or websearch_to_tsquery('english', '"Green Eggs"') or websearch_to_tsquery('english', 'animal -rabbit').

Create full text search index with generated column

To create an index for full text search, add a tsvector column to store the searchable index: ALTER TABLE books ADD COLUMN fts tsvector GENERATED ALWAYS AS (to_tsvector('english', description || ' ' || title)) STORED; Then create a GIN index: CREATE INDEX books_fts ON books USING gin (fts);

<-> is the proximity operator for full text search

The <-> operator finds terms that are a certain 'distance' apart. For immediate adjacency: to_tsquery('big <-> dreams') finds 'big' immediately followed by 'dreams'. For distance: to_tsquery('year <2> school') finds 'year' and 'school' within 2 words of each other.

! operator for negation in full text search

Use the ! symbol to exclude terms from search results. Example: to_tsquery('big & !little') finds documents containing 'big' but NOT 'little'.

ts_rank() function for ranking search results

Use ts_rank() to compute relevance scores for search results. Example: ts_rank(to_tsvector('english', description), to_tsquery(search_query)) returns a real number score. Order results by rank desc to show most relevant matches first.

Weighted tsvector columns with setweight() for ranking

Use setweight() to assign importance levels to different parts of documents. Postgres uses four weight labels: A (highest, 1.0), B (high, 0.4), C (medium, 0.2), D (low, 0.1). Example: setweight(to_tsvector('english', title), 'A') || setweight(to_tsvector('english', description), 'B') gives titles higher priority than descriptions.

ts_rank() with custom weight array

Specify custom weights to ts_rank() with a weight array: ts_rank('{0.0, 0.2, 0.5, 1.0}'::real[], fts_weighted, to_tsquery(query)). The array format is {D, C, B, A} where each element corresponds to the weight label importance.

Search multiple columns by concatenating with ||

To search across multiple columns in SQL, concatenate them with a space separator: to_tsvector(description || ' ' || title) @@ to_tsquery('search_term'). Include a space between columns to separate tokens properly.

Computed columns for multi-column search in supabase-js

To search multiple columns using supabase-js, create a computed column (virtual column) on the database. Define a Postgres function like: CREATE FUNCTION title_description(books) RETURNS text AS $$ SELECT $1.title || ' ' || $1.description; $$ LANGUAGE sql IMMUTABLE; Then use textSearch() on the computed column name.

Give your agent this brain