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

学习openGauss的第二十天。
主要内容是学习openGauss全文索引
openGauss提供了两种数据类型用于支持全文检索。tsvector类型表示为文本搜索优化的文件格式，tsquery类型表示文本查询


连接数据库
su - omm
gsql -r

1.用tsvector @@ tsquery和tsquery @@ tsvector完成两个基本文本匹配

SELECT 'abc sdf ert dfgh sf eew gbv ewx'::tsvector @@ 'sdf & gbv'::tsquery AS RESULT;
SELECT 'sdf & gbv'::tsquery @@ 'abc sdf ert dfgh sf eew gbv ewx'::tsvector AS RESULT;

omm=# SELECT 'abc sdf ert dfgh sf eew gbv ewx'::tsvector @@ 'sdf & gbv'::tsquery AS RESULT;
result
--------
t
(1 row)

omm=# SELECT 'sdf & gbv'::tsquery @@ 'abc sdf ert dfgh sf eew gbv ewx'::tsvector AS RESULT;
result
--------
t
(1 row)

2.创建表且至少有两个字段的类型为 text类型，在创建索引前进行全文检索

create table con_tab(col1 int, col2 text, col3 text);

insert into con_tab values(1, 'There are moments in life when you miss someone','so much that you just want to pick them');
insert into con_tab values(2, 'Dream what you want to dream','go where you want to go;be what you want to be');
insert into con_tab values(3, 'because you have only one life and','one chance to do all the things you want to do');

select * from con_tab where to_tsvector(col2) @@ to_tsquery('want');

omm=# create table con_tab(col1 int, col2 text, col3 text);
CREATE TABLE
omm=# insert into con_tab values(1, 'There are moments in life when you miss someone','so much that you just want to pick them');
INSERT 0 1
omm=# insert into con_tab values(2, 'Dream what you want to dream','go where you want to go;be what you want to be');
INSERT 0 1
omm=# insert into con_tab values(3, 'because you have only one life and','one chance to do all the things you want to do');
INSERT 0 1

omm=# select to_tsvector(col2) FROM con_tab ;
to_tsvector
-----------------------------------------
'life':5 'miss':8 'moment':3 'someon':9
'dream':1,6 'want':4
'life':6 'one':5
(3 rows)

omm=# SELECT * FROM con_tab WHERE to_tsvector(col2) @@ to_tsquery('want');
col1 | col2 | col3

------+------------------------------+-------------------------------------------

2 | Dream what you want to dream | go where you want to go;be what you want to be

(1 row)


3.创建GIN索引

create index con_idx on con_tab using gin(to_tsvector('english', col2));

omm=# create index con_idx on con_tab using gin(to_tsvector('english', col2));
CREATE INDEX
4.清理数据

drop table con_tab ;