暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

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

原创 手机用户2761 2021-12-21
539

学习心得

openGauss提供了两种数据类型用于支持全文检索。tsvector类型表示为文本搜索优化的文件格式,tsquery类型表示文本查询.
@@ : 匹配
to_tsquery : 转化为存储查询条件
to_tsvector: 转化为文本搜索优化的文件格式
GIN : Generalized Inverted Index函数

0.进入系统

su - omm
gsql -r

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

用tsvector @@ tsquery完成基本文本匹配

SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ 'cat & rat'::tsquery AS RESULT;
  • 回显
 result
--------
 t
(1 row)

用tsquery @@ tsvector完成基本文本匹配

SELECT 'fat & cow'::tsquery @@ 'a fat cat sat on a mat and ate a fat rat'::tsvector AS RESULT;
  • 回显
 result
--------
 f
(1 row)

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

CREATE SCHEMA s1;
CREATE TABLE s1.pgweb(id int, body text, title text, last_mod_date date);
INSERT INTO s1.pgweb VALUES(1, 'China, officially the People''s Republic of China(PRC), located in Asia, is the world''s most populous state.', 'China', '2010-1-1');

INSERT INTO s1.pgweb VALUES(2, 'America is a rock band, formed in England in 1970 by multi-instrumentalists Dewey Bunnell, Dan Peek, and Gerry Beckley.', 'America', '2010-1-1');

INSERT INTO s1.pgweb VALUES(3, 'England is a country that is part of the United Kingdom. It shares land borders with Scotland to the north and Wales to the west.', 'England','2010-1-1');

SELECT id, body, title FROM s1.pgweb WHERE to_tsvector(body) @@ to_tsquery('america');
  • 回显
 id | body |  title
----+-------------------------------------------------------------------------------------------------------------------------+---------
  2 | America is a rock band, formed in England in 1970 by multi-instrumentalists Dewey Bunnell, Dan Peek, and Gerry Beckley. | America
(1 row)

3. 创建GIN索引

创建pgweb_idx_1并查看

CREATE INDEX pgweb_idx_1 ON s1.pgweb USING gin(to_tsvector('english', body));
\d+ s1.pgweb
  • 回显
CREATE INDEX
                              Table "s1.pgweb"
    Column     |  Type   | Modifiers | Storage  | Stats target | Description
---------------+---------+-----------+----------+--------------+-------------
 id            | integer |           | plain    |              |
 body          | text    |           | extended |              |
 title         | text    |           | extended |              |
 last_mod_date | date    |           | plain    |              |
Indexes:
    "pgweb_idx_1" gin (to_tsvector('english'::regconfig, body)) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

4. 清理数据

DROP TABLE s1.pgweb;
DROP SCHEMA s1 CASCADE;
  • 回显
DROP TABLE
DROP SCHEMA
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论