Postgres全文索引

Postgres 分词插件安装脚本

#!/bin/bash
cd /usr/local/src
wget http://www.xunsearch.com/scws/down/scws-1.2.2.tar.bz2
tar -jxv -f scws-1.2.2.tar.bz2
cd scws-1.2.2
./configure
make && make install

cd /usr/local/src
git clone https://github.com/amutu/zhparser.git
cd zhparser
SCWS_HOME=/usr/local make && make install

创建全文索引配置

CREATE EXTENSION zhparser;
CREATE TEXT SEARCH CONFIGURATION renren.nickname_cfg (
  PARSER = zhparser
);
ALTER TEXT SEARCH CONFIGURATION renren.nickname_cfg ADD MAPPING FOR a WITH simple;
ALTER TEXT SEARCH CONFIGURATION renren.nickname_cfg ADD MAPPING FOR b WITH simple;
ALTER TEXT SEARCH CONFIGURATION renren.nickname_cfg ADD MAPPING FOR c WITH simple;
ALTER TEXT SEARCH CONFIGURATION renren.nickname_cfg ADD MAPPING FOR d WITH simple;
ALTER TEXT SEARCH CONFIGURATION renren.nickname_cfg ADD MAPPING FOR e WITH simple;
ALTER TEXT SEARCH CONFIGURATION renren.nickname_cfg ADD MAPPING FOR f WITH simple;
ALTER TEXT SEARCH CONFIGURATION renren.nickname_cfg ADD MAPPING FOR g WITH simple;
ALTER TEXT SEARCH CONFIGURATION renren.nickname_cfg ADD MAPPING FOR h WITH simple;
ALTER TEXT SEARCH CONFIGURATION renren.nickname_cfg ADD MAPPING FOR i WITH simple;
ALTER TEXT SEARCH CONFIGURATION renren.nickname_cfg ADD MAPPING FOR j WITH simple;
ALTER TEXT SEARCH CONFIGURATION renren.nickname_cfg ADD MAPPING FOR k WITH simple;
ALTER TEXT SEARCH CONFIGURATION renren.nickname_cfg ADD MAPPING FOR l WITH simple;
ALTER TEXT SEARCH CONFIGURATION renren.nickname_cfg ADD MAPPING FOR m WITH simple;
ALTER TEXT SEARCH CONFIGURATION renren.nickname_cfg ADD MAPPING FOR n WITH simple;
ALTER TEXT SEARCH CONFIGURATION renren.nickname_cfg ADD MAPPING FOR o WITH simple;
ALTER TEXT SEARCH CONFIGURATION renren.nickname_cfg ADD MAPPING FOR p WITH simple;
ALTER TEXT SEARCH CONFIGURATION renren.nickname_cfg ADD MAPPING FOR q WITH simple;
ALTER TEXT SEARCH CONFIGURATION renren.nickname_cfg ADD MAPPING FOR r WITH simple;
ALTER TEXT SEARCH CONFIGURATION renren.nickname_cfg ADD MAPPING FOR s WITH simple;
ALTER TEXT SEARCH CONFIGURATION renren.nickname_cfg ADD MAPPING FOR t WITH simple;
ALTER TEXT SEARCH CONFIGURATION renren.nickname_cfg ADD MAPPING FOR u WITH simple;
ALTER TEXT SEARCH CONFIGURATION renren.nickname_cfg ADD MAPPING FOR v WITH simple;
ALTER TEXT SEARCH CONFIGURATION renren.nickname_cfg ADD MAPPING FOR w WITH simple;
ALTER TEXT SEARCH CONFIGURATION renren.nickname_cfg ADD MAPPING FOR x WITH simple;
ALTER TEXT SEARCH CONFIGURATION renren.nickname_cfg ADD MAPPING FOR y WITH simple;

使用方式

select * from renren.user_nickname_search where fti_nickname @@ to_tsquery(array_to_string(array(select token from ts_parse('public.zhparser','那谁')),' & '));