PostgreSQL 英文字段全文检索

PostgreSQL 中文全文检索请看：https://blog.csdn.net/panguangyuu/article/details/90209806

一、创建测试表

create table testindex (
    id int not null primary key,
    txt text not null
); 

二、插入两条测试数据

insert into testindex values(1, 'hello world, i love you');
insert into testindex values(2, 'hello world, i hate you');

三、全文检索sql，查找到对应记录的id

select id from (
    select to_tsvector(testindex.txt) as fullsearch_txt, testindex.id from testindex
) as a where fullsearch_txt @@ 'world';

-- 找到包含表中name字段包含world的记录id

 id 
----
  1
  2
(2 rows)

select id from (
    select to_tsvector(testindex.txt) as fullsearch_txt, testindex.id from testindex
) as a where fullsearch_txt @@ 'love';

-- 找出包含love的

 id 
----
  1
(1 row)

文章参考：https://www.oschina.net/translate/postgres-full-text-search-is-good-enough