pg_bigm

pig build pkg pg_bigm;		# build rpm/deb

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

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

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

shared_preload_libraries = 'pg_bigm';

CREATE EXTENSION pg_bigm;

-- Create extension
CREATE EXTENSION pg_bigm;

-- Create a table with text data
CREATE TABLE documents (id serial PRIMARY KEY, content text);
INSERT INTO documents (content) VALUES
  ('PostgreSQL is a powerful database'),
  ('Full text search with bigram indexing'),
  ('Japanese text: 日本語テキスト検索');

-- Create a bigram index
CREATE INDEX docs_bigm_idx ON documents USING gin (content gin_bigm_ops);

-- Search using LIKE
SELECT * FROM documents WHERE content LIKE '%search%';

-- Search using likequery function
SELECT * FROM documents WHERE content LIKE likequery('database');

-- Show bigrams for a string
SELECT show_bigm('PostgreSQL');

-- Similarity search
SET pg_bigm.similarity_limit = 0.2;
SELECT * FROM documents WHERE content =% 'database search';