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

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

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.

omm=# SELECT 'this is a query test and this is correct testly'::tsvector @@ 'this & is'::tsquery AS RESULT;
 result 
--------
 t
(1 row)

omm=# SELECT 'this is a query test and this is correct testly'::tsvector @@ 'this & isas'::tsquery AS RESULT;
 result 
--------
 f
(1 row)

omm=# SELECT 'this & test & a'::tsquery @@ 'this is a query test and this is a correct test'::tsvector AS RESULT;
 result 
--------
 t
(1 row)

omm=# SELECT 'this & test & a'::tsquery @@ 'thisa isa a query test and thisa is a correct test'::tsvector AS RESULT;
 result 
--------
 f
(1 row)

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

omm=# omm-# (
omm(#         c1 int,
omm(# create table update_table
        c2 TEXT,
omm(# 
ANALYZE                    CREATE                     EXPLAIN                    PREPARE                    SECURITY LABEL             VACUUM
BEGIN                      DEALLOCATE                 FETCH                      REASSIGN                   SELECT                     VALUES
ABORT                      COMMIT                     END                        MOVE                       ROLLBACK                   UNLISTEN
ALTER                      COPY                       EXECUTE                    NOTIFY                     SAVEPOINT                  UPDATE
COMMENT                    DROP                       LOCK                       REVOKE                     TRUNCATE                   
omm(# c3 TEXT
omm(# );CALL                       DECLARE                    GRANT                      REFRESH MATERIALIZED VIEW  SET                        WITH
CHECKPOINT                 DELETE FROM                INSERT                     REINDEX                    SHOW                       
CLOSE                      DISCARD                    LISTEN                     RELEASE                    START                      
CLUSTER                    DO                         LOAD                       RESET                      TABLE                      

CREATE TABLE
omm=# INSERT INTO update_table VALUES
omm-# (1, 'this is a sentance', 'if you like it'),
omm-# (2, 'thisa isa warong', 'if youlike it'),
omm-# (3, 'thisis an sentance', 'if you likeit'),
omm-# (4, 'ths as is sentance', 'if you liket'),
omm-# (5, 'this like asentance', 'if  like it'),
omm-# (6, 'like is this sentance', 'alike like it'),
omm-# (7, 'this is correct', 'like it is'),
omm-# (8, 'thisentance', 'you like this'),
omm-# (9, 'thisance', 'you like it is '),
omm-# (10, 'thintance', 'ifyou likeit');
INSERT 0 10
omm=# SELECT c1, c2, c3 FROM update_table WHERE to_tsvector(c3) @@ to_tsquery('like');
 c1 |          c2           |       c3        
----+-----------------------+-----------------
  1 | this is a sentance    | if you like it
  5 | this like asentance   | if  like it
  6 | like is this sentance | alike like it
  7 | this is correct       | like it is
  8 | thisentance           | you like this
  9 | thisance              | you like it is 
(6 rows)

omm=# SELECT * FROM update_table WHERE to_tsvector(c3) @@ to_tsquery('sentance');
 c1 | c2 | c3 
----+----+----
(0 rows)

omm=# SELECT * FROM update_table WHERE to_tsvector(c) @@ to_tsquery('sentance');2) @@ to_tsquery('sentance');
 c1 |          c2           |       c3       
----+-----------------------+----------------
  1 | this is a sentance    | if you like it
  3 | thisis an sentance    | if you likeit
  4 | ths as is sentance    | if you liket
  6 | like is this sentance | alike like it
(4 rows)

omm=# SELECT * FROM update_table WHERE to_tsvector(c2) @@ to_tsquery('sentance & it');  
 c1 |          c2           |       c3       
----+-----------------------+----------------
  1 | this is a sentance    | if you like it
  3 | thisis an sentance    | if you likeit
  4 | ths as is sentance    | if you liket
  6 | like is this sentance | alike like it
(4 rows)

3.创建GIN索引

omm=# CREATE INDEX update_table_idx_1 ON update_table USING gin(to_tsvector('english', c3));
CREATE INDEX
omm=# 

4.清理数据

drop table update_table;