pg_textsearch

pig build pkg pg_textsearch         # build RPM / DEB packages

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

CREATE EXTENSION pg_textsearch;

CREATE EXTENSION pg_textsearch;

CREATE TABLE documents (id bigserial PRIMARY KEY, content text);
INSERT INTO documents (content) VALUES
    ('PostgreSQL is a powerful database system'),
    ('BM25 is an effective ranking function'),
    ('Full text search with custom scoring');

-- Create a BM25 index
CREATE INDEX docs_idx ON documents USING bm25(content) WITH (text_config='english');

-- Query using the <@> operator (returns negative BM25 score, lower = better match)
SELECT * FROM documents
ORDER BY content <@> 'database system'
LIMIT 5;

-- Auto-detect index from column
SELECT * FROM documents
ORDER BY content <@> 'database system'
LIMIT 5;

-- Explicit index specification
SELECT * FROM documents
WHERE content <@> to_bm25query('database system', 'docs_idx') < -1.0;

CREATE INDEX ON documents (category_id);
SELECT * FROM documents
WHERE category_id = 123
ORDER BY content <@> 'search terms'
LIMIT 10;

SELECT * FROM documents
WHERE content <@> to_bm25query('search terms', 'docs_idx') < -5.0
ORDER BY content <@> 'search terms'
LIMIT 10;

CREATE INDEX ON documents USING bm25(content)
  WITH (text_config='english', k1=1.5, b=0.8);

-- Language-specific configurations
CREATE INDEX ON french_docs USING bm25(content) WITH (text_config='french');
CREATE INDEX ON german_docs USING bm25(content) WITH (text_config='german');

SELECT to_bm25query('search query text', 'docs_idx');
-- docs_idx:search query text