pg_jieba (Chinese tokenization)
pg_jieba is an open source third-party extension for Chinese full text search.
Prerequisites
PolarDB for PostgreSQL supports the following versions:
PostgreSQL 15 (minor engine version 15.7.2.0 or later)
PostgreSQL 14 (minor engine version 14.5.2.0 or later)
PostgreSQL 11 (minor engine version 1.1.28 or later)
You can run the following statement to view the minor engine version of your PolarDB for PostgreSQL cluster:
PostgreSQL 15 and PostgreSQL 14
SELECT version();PostgreSQL 11
SHOW polar_version;
Introduction
The pg_jieba extension provides the following text search configurations:
jiebacfg: Accurate mode. This mode accurately splits the text without redundant words.
jiebaqry: Full mode. This mode scans for all possible matching words in the text. It may produce redundant words.
jiebacfg_pos: Accurate mode with position. This mode adds position index information to the results of the accurate mode. This mode also displays stop words, which are ignored in the accurate mode because they appear too frequently to be useful in searches.
Usage
Install or uninstall the extension
Only privileged accounts can run commands to install or uninstall the extension.
Install the extension:
CREATE EXTENSION pg_jieba;Uninstall the extension:
DROP EXTENSION pg_jieba;
Examples
Example 1
Use the jiebacfg accurate mode configuration:
SELECT * FROM to_tsvector('jiebacfg', 'Xiaoming, a master''s graduate from the Institute of Computing Technology, Chinese Academy of Sciences, later pursued advanced studies at Kyoto University in Japan');The following result is returned:
to_tsvector ---------------------------------------------------------------------------------- 'Chinese Academy of Sciences':5 'Xiaoming':1 'Kyoto University':10 'graduate':3 'advanced studies':11 'master''s':2 'Institute of Computing Technology':6 (1 row)Use the jiebaqry full mode configuration:
SELECT * FROM to_tsvector('jiebaqry', 'Xiaoming, a master''s graduate from the Institute of Computing Technology, Chinese Academy of Sciences, later pursued advanced studies at Kyoto University in Japan');The following result is returned:
to_tsvector ----------------------------------------------------------------------------------------------------------------------------------------------------------------- 'China':5 'Chinese Academy of Sciences':9 'Kyoto':16 'University':17 'Academy':7 'Xiaoming':1 'Japan':15 'Kyoto University':18 'graduate':3 'advanced studies':19 'master''s':2 'Science':6 'Academy of Sciences':8 'Computing':10 'Institute of Computing Technology':11 (1 row)Use the jiebacfg_pos accurate mode with position configuration:
SELECT * FROM to_tsvector('jiebacfg_pos', 'Xiaoming, a master''s graduate from the Institute of Computing Technology, Chinese Academy of Sciences, later pursued advanced studies at Kyoto University in Japan');The following result is returned:
to_tsvector ------------------------------------------------------------------------------------------------------------------------------------------ 'Chinese Academy of Sciences:7':5 'from:6':4 'later:16':8 'at:17':9 'Xiaoming:0':1 'Kyoto University:18':10 'graduate:4':3 'advanced studies:24':11 'master''s:2':2 'Institute of Computing Technology:12':6 ',:15':7 (1 row)
Example 2
Use the jiebacfg accurate mode configuration:
SELECT * FROM to_tsvector('jiebacfg', 'Li Xiaofu is the director of the innovation office and an expert in the cloud computing field');The following result is returned:
to_tsvector ------------------------------------------------------------------- 'expert':11 'director':5 'cloud computing':8 'innovation':3 'office':4 'field':9 'Li Xiaofu':1 (1 row)Use the jiebaqry full mode configuration:
SELECT * FROM to_tsvector('jiebaqry', 'Li Xiaofu is the director of the innovation office and an expert in the cloud computing field');The following result is returned:
to_tsvector ----------------------------------------------------------------------------- 'expert':12 'director':5 'cloud computing':9 'innovation':3 'office':4 'field':10 'Li Xiaofu':1 'computing':8 (1 row)Use the jiebacfg_pos accurate mode with position configuration:
SELECT * FROM to_tsvector('jiebacfg_pos', 'Li Xiaofu is the director of the innovation office and an expert in the cloud computing field');The following result is returned:
to_tsvector --------------------------------------------------------------------------------------------------------------------------- 'expert:17':11 'director:7':5 'also:9':6 'cloud computing:11':8 'innovation:4':3 'office:6':4 'field:14':9 'is:10':7 'is:3':2 'Li Xiaofu:0':1 'of:16':10 (1 row)
Extended features
pg_jieba lets you configure and switch between multiple custom dictionaries.
To use the custom dictionary feature, you must add pg_jieba to the shared_preload_libraries parameter. You can set this parameter in the console. For more information, see Set cluster parameters. Because modifying this parameter restarts the cluster, plan your operations accordingly and proceed with caution.
Insert data into dictionary 0. By default, dictionary 0 is used and has a weight of 10.
INSERT INTO jieba_user_dict VALUES ('Alibaba Cloud'); INSERT INTO jieba_user_dict VALUES ('R&D engineer',0,10);Tokenize text using the default Jieba dictionary.
SELECT * FROM to_tsvector('jiebacfg', 'zth is an R&D engineer at Alibaba Cloud');The following result is returned:
to_tsvector ------------------------------------------------------ 'zth':1 'an':6 'Cloud':4 'engineer':8 'R&D':7 'Ali':3 (1 row)Switch to custom dictionary 0.
SELECT jieba_load_user_dict(0);The following result is returned:
jieba_load_user_dict ---------------------- (1 row)Tokenize text using dictionary 0.
SELECT * FROM to_tsvector('jiebacfg', 'zth is an R&D engineer at Alibaba Cloud');The following result is returned:
to_tsvector -------------------------------------------- 'zth':1 'an':5 'R&D engineer':6 'Alibaba Cloud':3 (1 row)