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

本次课学习的内容是openGauss全文检索。

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

root@modb:~# 
root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

作业打卡

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

omm=# select 'the cat is the enemy of fat rats'::tsvector @@ 'fat & cat' ::tsquery AS RESULT;
 result 
--------
 t
(1 row)
omm=# select 'the cat is the enemy of fat rats'::tsvector @@'enemy & cow' AS RESULT;
omm=#  result 
--------
 f
(1 row)

omm=# SELECT to_tsvector('the cat is the enemy of fat rats') @@ to_tsquery('fat & cat') AS RESULT;
 result 
--------
 t
(1 row)

omm=# SELECT to_tsvector('the cat is the enemy of fat rats') @@ to_tsquery('enemy & cow') AS RESULT;
 result 
--------
 f
(1 row)

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

omm=# create table t1
omm-# (
omm(# t_id int,
omm(# t_name char(20),
omm(# t_1 text,
omm(# t_2 text
omm(# );
CREATE TABLE
omm=# insert into t1 values(100,'Zhang Peng','Zhang Peng is eating noodles','Zhang Peng like playing basketball'),
omm-# (200,'Li Xin','Li Xin is defending his high towel','Li Xin is golden and dark'),
omm-# (300,'Yao','Yao can be a boy or a girl','Yao is naughty');
INSERT 0 3
omm=# select t_id,t_name,t_1,t_2 from t1 where to_tsvector(t_1) @@ to_tsquery('Li');       
 t_id |        t_name        |                t_1                 |            t_2            
------+----------------------+------------------------------------+---------------------------
  200 | Li Xin               | Li Xin is defending his high towel | Li Xin is golden and dark
(1 row)

3、创建GIN索引

omm=# CREATE INDEX index_1 on t1 USING gin(to_tsvector('english',t_1));
CREATE INDEX
omm=# CREATE INDEX index_2 on t1 USING gin(to_tsvector('english',t_2 || ' ' || t_1));
omm=# CREATE INDEX
omm=# \d+ t1
                             Table "public.t1"
 Column |     Type      | Modifiers | Storage  | Stats target | Description 
--------+---------------+-----------+----------+--------------+-------------
 t_id   | integer       |           | plain    |              | 
 t_name | character(20) |           | extended |              | 
 t_1    | text          |           | extended |              | 
 t_2    | text          |           | extended |              | 
Indexes:
    "index_1" gin (to_tsvector('english'::regconfig, t_1)) TABLESPACE pg_default
    "index_2" gin (to_tsvector('english'::regconfig, (t_2 || ' '::text) || t_1)) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

4、清理数据

omm=# drop table t1;
DROP TABLE