Postgresql全文索引

User:Administrator

Last Update:2020-9-16 13:58


### Code Reference
  • URL:https://www.digitalocean.com/community/tutorials/how-to-use-full-text-search-in-postgresql-on-ubuntu-16-04#step-1-%E2%80%94-creating-example-data

  • DESC:Postgresql全文索引

  • Last Update:2020-9-16 13:58

  • Time:2020-9-16 13:58 Tittle:Postgresql全文索引

  • Version:001

    Postgresql全文索引

    to_tsvector用于创建令牌列表(tsvector数据类型,其中ts“文本搜索”代表);
    to_tsquery 用于查询向量中是否存在某些单词或短语。
    示例:

      -- 创建测试表
      CREATE TABLE news (
         id SERIAL PRIMARY KEY,
         title TEXT NOT NULL,
         content TEXT NOT NULL,
         author TEXT NOT NULL
      );
      INSERT INTO news (id, title, content, author) VALUES
      (1, 'Pacific Northwest high-speed rail line', 'Currently there are only a few options for traveling the 140 miles between Seattle and Vancouver and none of them are ideal.', 'Greg'),
      (2, 'Hitting the beach was voted the best part of life in the region', 'Exploring tracks and trails was second most popular, followed by visiting the shops and then checking out local parks.', 'Ethan'),
      (3, 'Machine Learning from scratch', 'Bare bones implementations of some of the foundational models and algorithms.', 'Jo');
    
    
      SELECT title || '. ' || content as document
            ,to_tsvector(title || '. ' || content) as metadata
        FROM news
       WHERE id = 1;
    
      -- 使用搜索条件(&,|,!)
      SELECT *
        FROM news
       WHERE to_tsvector(title || '. ' || content) @@ to_tsquery('Bare | some | Pacific | Exploring');
    
      -- 优化性能使用gin
    
      ALTER TABLE news ADD "document" tsvector;
    
      INSERT INTO news (id, title, content, author, document)
      VALUES (4, 'Sleep deprivation curing depression'
               , 'Clinicians have long known that there is a strong link between sleep, sunlight and mood.'
               , 'Patel', to_tsvector('Sleep deprivation curing depression' || '. ' || 'Clinicians have long known that there is a strong link between sleep,
               sunlight and mood.'));
    
      UPDATE news
         SET document = to_tsvector(title || '. ' || content)
       WHERE document IS NULL;
    
      CREATE INDEX idx_fts_search ON news USING gin(document);
    
      SELECT title, content
        FROM news
       WHERE document @@ to_tsquery('Travel | Cure');
    
已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 创作都市 设计师:CSDN官方博客 返回首页