-- 创建全文搜索列
ALTER TABLE articles ADD COLUMN search_vector tsvector;

-- 更新搜索向量
UPDATE articles SET search_vector =
    to_tsvector('english', title || ' ' || content);

-- 创建GIN索引
CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);

-- 搜索
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql & database');

-- 相关性排序
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'postgresql') query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- 安装zhparser分词器
CREATE EXTENSION zhparser;
CREATE TEXT SEARCH CONFIGURATION chinese (PARSER = zhparser);

-- 使用中文搜索
SELECT * FROM articles
WHERE to_tsvector('chinese', title) @@ to_tsquery('chinese', '数据库');