pg_search

pig build pkg pg_search;		# build deb

pig repo add pgsql -u   # add both repo and update cache

pig install pg_search;		# install via package name, for the active PG version

pig install pg_search -v 18;   # install for PG 18
pig install pg_search -v 17;   # install for PG 17
pig install pg_search -v 16;   # install for PG 16
pig install pg_search -v 15;   # install for PG 15

CREATE EXTENSION pg_search;

CREATE EXTENSION pg_search;

ALTER SYSTEM SET paradedb.pg_search_telemetry TO 'off';

CALL paradedb.create_bm25_test_table(
  schema_name => 'public',
  table_name => 'mock_items'
);

SELECT description, rating, category FROM mock_items LIMIT 3;

-- Create a BM25 index (key_field must be UNIQUE, one BM25 index per table)
CREATE INDEX search_idx ON mock_items
USING bm25 (id, description, category, rating, in_stock, created_at, metadata, weight_range)
WITH (key_field='id');

-- Full-text search with @@@ operator
SELECT description, rating, category
FROM mock_items
WHERE description @@@ 'keyboard' AND rating > 2
ORDER BY rating
LIMIT 5;

-- BM25 relevance scoring
SELECT description, paradedb.score(id)
FROM mock_items
WHERE description @@@ 'keyboard'
ORDER BY paradedb.score(id) DESC
LIMIT 5;

-- Highlighting matched terms
SELECT description, paradedb.snippet(description), paradedb.score(id)
FROM mock_items
WHERE description @@@ 'keyboard'
ORDER BY paradedb.score(id) DESC
LIMIT 5;

-- Exact phrase search (use double quotes inside single quotes)
SELECT description, rating, category
FROM mock_items
WHERE description @@@ '"metal keyboard"';

-- Configure text fields with tokenizers (e.g., English stemming)
DROP INDEX search_idx;
CREATE INDEX search_idx ON mock_items
USING bm25 (id, (description::pdb.simple('stemmer=english')), category)
WITH (key_field='id');