PT

to_tsquery throws a syntax error on user input

Postgres full-text search breaks the first time someone types an ampersand or a stray quote, because to_tsquery takes an expression, not text. websearch_to_tsquery never raises, and prefix search needs a different query.

Your search box works for weeks and then starts returning 500s. The log is short:

error: syntax error in tsquery: "wifi &"
error: syntax error in tsquery: "c++"
error: syntax error in tsquery: "don't"

Someone typed an ampersand. Someone searched for a language with pluses in its name. Someone used an apostrophe. None of that is malformed input from a user's point of view — it is what people type into search boxes — and all of it crashes to_tsquery.

Why it raises

to_tsquery does not take text. It takes a tsquery expression: a small boolean language with & for and, | for or, ! for not, <-> for followed-by, parentheses for grouping and :* for prefix matching. Passing raw user input to it is like passing raw user input to CAST(… AS json): the first character that is not valid in that grammar is an error, not a word.

select to_tsquery('english', 'wifi &');
-- ERROR:  syntax error in tsquery: "wifi &"

select to_tsquery('english', 'wifi & password');
--      'wifi' & 'password'      -- fine, because this *is* an expression

The temptation is to sanitise with a regex — strip &|!()<>:*', collapse whitespace, try again. That turns a crash into a slow leak of wrong behaviour: c++ becomes c, AT&T becomes two words, and you will keep discovering characters for months. The real problem is the choice of function.

The three input functions, and when each is right

Postgres ships three ways to turn a string into a tsquery:

Function Input Raises on bad input Behaviour
to_tsquery an expression yes you build the operators
plainto_tsquery text no ANDs every word
websearch_to_tsquery text no the syntax users already know

plainto_tsquery is safe but blunt: every word becomes a required term and there is no way for the user to ask for a phrase or an alternative.

websearch_to_tsquery is the one you almost always want for a search box. It accepts the conventions people already learned from web search engines and it never raises:

select websearch_to_tsquery('english', 'wifi &');
--      'wifi'

select websearch_to_tsquery('english', '"cookie consent" -analytics');
--      'cooki' <-> 'consent' & !'analytic'

select websearch_to_tsquery('english', 'postgres or mysql');
--      'postgr' | 'mysql'

Quoted phrases become adjacency, a leading - becomes negation, or becomes alternation, and anything it cannot parse it simply discards. The 500 disappears without a sanitiser.

And whichever you pick: pass the query as a parameter, never concatenate it into the SQL string. These functions protect you from tsquery syntax errors; they do not protect you from SQL injection, because at that point the damage is already in the statement text.

The column, the index and the query

Store the vector instead of computing it per query, in a generated column:

alter table documents
  add column search_vector tsvector
  generated always as (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body,  '')), 'B')
  ) stored;

create index documents_search_idx on documents using gin (search_vector);

Note the two-argument to_tsvector('english', …). The one-argument form resolves the dictionary from the default_text_search_config session setting, which makes it non-immutable, so Postgres refuses it in a generated column or an index definition. If you ever saw "generation expression is not immutable", that is why. coalesce matters too: concatenating a NULL tsvector makes the whole vector NULL, so a row with no body would never match anything.

setweight is what lets a title hit outrank a body hit later.

select d.id,
       d.title,
       ts_rank_cd(d.search_vector, q.query) as rank,
       ts_headline('english', d.body, q.query,
                   'StartSel=<<, StopSel=>>, MaxFragments=2, MinWords=8, MaxWords=24') as snippet
  from documents d,
       websearch_to_tsquery('english', $1) as q(query)
 where d.search_vector @@ q.query
 order by rank desc, d.id
 limit 20;

ts_rank_cd (cover density) takes proximity into account, so a document where the terms appear next to each other ranks above one where they are paragraphs apart. Plain ts_rank only counts frequency and weights.

One guard: websearch_to_tsquery returns an empty query for input that contains no usable words, and search_vector @@ ''::tsquery matches nothing. That is the behaviour you want — an empty result, not an error — but check for it before you run the query if you would rather show "type something" than zero hits.

Snippets are not HTML

ts_headline wraps matches in <b> by default and returns the surrounding text unescaped. If body contains anything a user wrote, rendering that string with dangerouslySetInnerHTML or v-html is a stored XSS. The fix is in the query above: set StartSel and StopSel to sentinels that cannot appear in your content, then split in the application and let the framework escape each piece:

function Snippet({ text }: { text: string }) {
  const parts = text.split(/<<|>>/);
  return (
    <>
      {parts.map((part, i) =>
        i % 2 === 1 ? <mark key={i}>{part}</mark> : <span key={i}>{part}</span>,
      )}
    </>
  );
}

Odd indices are inside a match because split alternates. No raw HTML crosses the boundary, and you can style <mark> however you like.

Search-as-you-type needs a different query

websearch_to_tsquery has no prefix operator, so typing cook will not find cookie — the stemmer reduces whole words, it does not truncate them. For an autocomplete you want the completed words treated normally and only the last word treated as a prefix. Build that term with quote_literal, which escapes embedded quotes into a valid tsquery lexeme:

-- $1 = everything before the last word, $2 = the word being typed
select websearch_to_tsquery('english', $1)
       && to_tsquery('simple', quote_literal($2) || ':*') as query;

quote_literal('don''t') produces 'don''t', which to_tsquery parses as a single lexeme instead of choking on the apostrophe. The simple configuration is deliberate: you do not want the prefix stemmed before it is truncated.

Typos are a separate problem

Full-text search matches stems, not approximations: postgers will never find postgres. If you need that, add a trigram fallback that only runs when the full-text query returned nothing:

create extension if not exists pg_trgm;
create index documents_title_trgm_idx on documents using gin (title gin_trgm_ops);

select id, title from documents
 where title % $1                       -- similarity above pg_trgm.similarity_threshold
 order by similarity(title, $1) desc
 limit 10;

Keep it on titles. Trigram similarity over long bodies is expensive and the results are rarely what the user meant.

One stemmer per language

to_tsvector('english', 'corriendo') stems with English rules and produces nonsense for a Spanish document. If your content is multilingual, store the language per row and index per language — a generated column needs a constant configuration, so that means either one partial index per language or a stored regconfig column plus a trigger. Whichever you choose, pass the same configuration at query time that you used at index time, or the index will not be used and the stems will not line up.

If you would rather not maintain this

@core/search is this design as source code in your repo: one search_documents table fed by any registered source, title and body vectors weighted and stemmed per document language, prefix matching for search-as-you-type, snippets returned as plain parts (never HTML, so there is nothing to escape) and the pg_trgm typo fallback when the extension is present. No external engine.

genpm add @core/search

It installs into src/lib/search, with adapters for Hono and Next.js and a reindex job for @core/jobs. Without the CLI installed: npx -y @genpm-net/cli add @core/search.