openGauss每日一练第20天 | 学习openGauss全文检索

openGauss提供了两种数据类型用于支持全文检索。tsvector类型表示为文本搜索优化的文件格式，tsquery类型表示文本查询

-- 1.
select 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ 'cat & rat'::tsquery as result;
select 'fat & cow'::tsquery @@ 'a fat cat sat on a mat and ate a fat rat'::tsvector as result;

-- 2.
create table test(id int, name text, home text);
insert into test values(1, 'China, officially the Peoples Republic of China(PRC)', 'China'),(2, 'America is a rock band, formed in England in 1970 by multi-instrumentalists Dewey Bunnell, Dan Peek, and Gerry Beckley.', 'America'),(3, 'England is a country that is part of the United Kingdom.', 'England');
select * from test;
select * from test where to_tsvector(name) @@ to_tsquery('china');
-- 将 name 字段中包含 china 的行打印出来

-- 3.
create index test_idx on test using gin(to_tsvector('english', name));

-- 4.
drop table test;