Implementing Full-Text Search

SELECT 'The quick brown fox'::tsvector;
SELECT to_tsvector('english', 'The quick brown foxes jumped');
-- 'brown':3 'fox':4 'jump':5 'quick':2

SELECT 'fox & jump'::tsquery;
SELECT to_tsquery('english', 'jumping & !lazy');

ALTER TABLE articles ADD COLUMN tsv tsvector
   GENERATED ALWAYS AS (to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''))) STORED;

SELECT to_tsvector('english', 'PostgreSQL full-text search');

SELECT to_tsquery('english', 'postgres & search');
SELECT to_tsquery('english', 'post:*');     -- prefix match

SELECT plainto_tsquery('english', 'quick brown fox');  -- 'quick' & 'brown' & 'fox'

SELECT phraseto_tsquery('english', 'quick brown fox');  -- 'quick' <-> 'brown' <-> 'fox'

SELECT websearch_to_tsquery('english',
   '"quick brown" -lazy OR dog');

SELECT id, title FROM articles
 WHERE tsv @@ websearch_to_tsquery('english', 'postgres "full text"');

CREATE INDEX articles_tsv_gin ON articles USING gin (tsv);

CREATE INDEX articles_tsv_gist ON articles USING gist (tsv);

SELECT id, title,
   ts_rank(tsv, q) AS rank,
   ts_rank_cd(tsv, q) AS rank_cd
  FROM articles, websearch_to_tsquery('english','postgres') q
 WHERE tsv @@ q ORDER BY rank DESC LIMIT 20;

SELECT ts_headline('english', body, q,
   'StartSel=<b>,StopSel=</b>,MaxFragments=2')
  FROM articles, websearch_to_tsquery('english','postgres') q
 WHERE tsv @@ q;

SELECT cfgname FROM pg_ts_config;
SELECT to_tsvector('spanish', 'corriendo rápido');

CREATE TEXT SEARCH CONFIGURATION my_en (COPY = english);
ALTER TEXT SEARCH CONFIGURATION my_en
   ALTER MAPPING FOR asciiword, word WITH unaccent, english_stem;