pg_bigm

pig build pkg pg_bigm         # 构建 RPM / DEB 包

pig repo add pgsql -u          # 添加仓库并更新缓存

shared_preload_libraries = 'pg_bigm';

CREATE EXTENSION pg_bigm;

-- 创建扩展
CREATE EXTENSION pg_bigm;

-- 创建含文本数据的表
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: 日本語テキスト検索');

-- 创建 bigram 索引
CREATE INDEX docs_bigm_idx ON documents USING gin (content gin_bigm_ops);

-- 使用 LIKE 搜索
SELECT * FROM documents WHERE content LIKE '%search%';

-- 使用 likequery 函数搜索
SELECT * FROM documents WHERE content LIKE likequery('database');

-- 显示字符串的 bigram
SELECT show_bigm('PostgreSQL');

-- 相似度搜索
SET pg_bigm.similarity_limit = 0.2;
SELECT * FROM documents WHERE content =% 'database search';