课程作业

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

SELECT 'I am a chinese'::tsvector @@ 'a & chinese'::tsquery AS RESULT;
SELECT 'a & chinese'::tsquery @@ 'I am a chinese'::tsvector AS RESULT;

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

CREATE TABLE ft_test(id int, body text, title text, last_mod_date date);

INSERT INTO ft_test 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 ft_test 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 ft_test 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 * from ft_test where to_tsvector(body) @@ to_tsquery('China');
select * from ft_test where to_tsvector(body) @@ to_tsquery('English');

3.创建GIN索引

CREATE INDEX idx_ft_1 ON ft_test USING gin(to_tsvector('english', body));

4.清理数据

DROP TABLE ft_test cascade;