课程作业
1.用tsvector @@ tsquery和tsquery @@ tsvector完成两个基本文本匹配
omm=# SELECT 'test kaoshi zuoye homeworks'::tsvector @@ 'zuoye'::tsquery AS RESULT;
omm=# result
--------
t
(1 row)
omm=# SELECT 'zuoye'::tsquery @@ 'test kaoshi zuoye homeworks'::tsvector AS RESULT;
result
--------
t
(1 row)
omm=# SELECT to_tsvector('test kaoshi zuoye homeworks') @@ to_tsquery('homework') AS RESULT;
result
--------
t
(1 row)
omm=# SELECT to_tsvector('test kaoshi zuoye homeworks') @@ to_tsquery('home') AS RESULT;
result
--------
f
(1 row)
2.创建表且至少有两个字段的类型为 text类型，在创建索引前进行全文检索
omm=# create schema tao;
CREATE SCHEMA
omm=# create table tao.test(id int,test1 text,test2 text);
CREATE TABLE
omm=# INSERT INTO tao.test VALUES(1, 'Hello,World!','nihao,shijie');
omm=# INSERT 0 1
omm=# INSERT INTO tao.test VALUES(2, 'Say Hi!', 'shuo hai');
omm=# INSERT 0 1
omm=# INSERT INTO tao.test VALUES(3, 'goodbye', 'baibai');
omm=# INSERT 0 1
omm=# SELECT id, test1, test2 FROM tao.test WHERE to_tsvector(test1) @@ to_tsquery('hi');
id | test1 | test2
----+---------+----------
2 | Say Hi! | shuo hai
(1 row)
omm=# SELECT id, test1, test2 FROM tao.test WHERE to_tsvector(test1 || ' ' || test2) @@ to_tsquery('world & nihao');
id | test1 | test2
----+--------------+--------------
1 | Hello,World! | nihao,shijie
(1 row)
3.创建GIN索引
omm=# CREATE INDEX test_idx ON tao.test USING gin(to_tsvector('english', test1));
CREATE INDEX
omm=# \d+ tao.test
Table "tao.test"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+----------+--------------+-------------
id | integer | | plain | |
test1 | text | | extended | |
test2 | text | | extended | |
Indexes:
"test_idx" gin (to_tsvector('english'::regconfig, test1)) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no
4.清理数据
omm=# drop schema tao cascade;
NOTICE: drop cascades to table tao.test
DROP SCHEMA
