详细讲解PostgreSQL中的全文搜索的用法_数据库其它_

 CREATE TABLE author( id SERIAL PRIMARY KEY, name TEXT NOT NULL); CREATE TABLE post( id SERIAL PRIMARY KEY, title TEXT NOT NULL, content TEXT NOT NULL, author_id INT NOT NULL references author(id) ); CREATE TABLE tag( id SERIAL PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE posts_tags( post_id INT NOT NULL references post(id), tag_id INT NOT NULL references tag(id) ); INSERT INTO author (id, name) VALUES (1, 'Pete Graham'), (2, 'Rachid Belaid'), (3, 'Robert Berry'); INSERT INTO tag (id, name) VALUES (1, 'scifi'), (2, 'politics'), (3, 'science'); INSERT INTO post (id, title, content, author_id) VALUES (1, 'Endangered species', 'Pandas are an endangered species', 1 ), (2, 'Freedom of Speech', 'Freedom of speech is a necessary right missing in many countries', 2), (3, 'Star Wars vs Star Trek', 'Few words from a big fan', 3); INSERT INTO posts_tags (post_id, tag_id) VALUES (1, 3), (2, 2), (3, 1); 

 SELECT post.title || ' ' || post.content || ' ' || author.name || ' ' || coalesce((string_agg(tag.name, ' ')), '') as document FROM post JOIN author ON author.id = post.author_id JOIN posts_tags ON posts_tags.post_id = posts_tags.tag_id JOIN tag ON tag.id = posts_tags.tag_id GROUP BY post.id, author.id; document -------------------------------------------------- Endangered species Pandas are an endangered species Pete Graham politics Freedom of Speech Freedom of speech is a necessary right missing in many countries Rachid Belaid politics Star Wars vs Star Trek Few words from a big fan Robert Berry politics (3 rows) 

 SELECT to_tsvector(post.title) || to_tsvector(post.content) || to_tsvector(author.name) || to_tsvector(coalesce((string_agg(tag.name, ' ')), '')) as documentFROM post JOIN author ON author.id = post.author_id JOIN posts_tags ON posts_tags.post_id = posts_tags.tag_id JOIN tag ON tag.id = posts_tags.tag_id GROUP BY post.id, author.id; document -------------------------------------------------- 'endang':1,6 'graham':9 'panda':3 'pete':8 'polit':10 'speci':2,7 'belaid':16 'countri':14 'freedom':1,4 'mani':13 'miss':11 'necessari':9 'polit':17 'rachid':15 'right':10 'speech':3,6 'berri':13 'big':10 'fan':11 'polit':14 'robert':12 'star':1,4 'trek':5 'vs':3 'war':2 'word':7 (3 rows) 

 SELECT to_tsvector('Try not to become a man of success, but rather try to become a man of value'); 

 to_tsvector ---------------------------------------------------------------------- 'becom':4,13 'man':6,15 'rather':10 'success':8 'tri':1,11 'valu':17(1 row) 

 > select to_tsvector('If you can dream it, you can do it') @@ 'dream'; ?column? ---------- t (1 row) > select to_tsvector('It''s kind of fun to do the impossible') @@ 'impossible'; ?column? ---------- f (1 row) 

 SELECT 'impossible'::tsquery, to_tsquery('impossible'); tsquery | to_tsquery --------------+------------ 'impossible' | 'imposs'(1 row) 

 SELECT 'dream'::tsquery, to_tsquery('dream'); tsquery | to_tsquery --------------+------------ 'dream' | 'dream'(1 row) 

 SELECT to_tsvector('It''s kind of fun to do the impossible') @@ to_tsquery('impossible'); ?column? ---------- t (1 row) 

 > SELECT to_tsvector('If the facts don't fit the theory, change the facts') @@ to_tsquery('! fact'); ?column? ---------- f (1 row) > SELECT to_tsvector('If the facts don''t fit the theory, change the facts') @@ to_tsquery('theory & !fact'); ?column? ---------- f (1 row) > SELECT to_tsvector('If the facts don''t fit the theory, change the facts.') @@ to_tsquery('fiction | theory'); ?column? ---------- t (1 row) 

 > SELECT to_tsvector('If the facts don''t fit the theory, change the facts.') @@ to_tsquery('theo:*'); ?column? ---------- t (1 row) 

 SELECT pid, p_titleFROM (SELECT post.id as pid, post.title as p_title, to_tsvector(post.title) || to_tsvector(post.content) || to_tsvector(author.name) || to_tsvector(coalesce(string_agg(tag.name, ' '))) as document FROM post JOIN author ON author.id = post.author_id JOIN posts_tags ON posts_tags.post_id = posts_tags.tag_id JOIN tag ON tag.id = posts_tags.tag_id GROUP BY post.id, author.id) p_search　WHERE p_search.document @@ to_tsquery('Endangered & Species'); pid | p_title -----+-------------------- 1 | Endangered species (1 row) 

 SELECT to_tsvector('english', 'We are running'); to_tsvector------------- 'run':3 (1 row)SELECT to_tsvector('french', 'We are running'); to_tsvector---------------------------- 'are':2 'running':3 'we':1 (1 row) 

 SELECT to_tsvector(post.language::regconfig, post.title) || to_tsvector(post.language::regconfig, post.content) || to_tsvector('simple', author.name) || to_tsvector('simple', coalesce((string_agg(tag.name, ' ')), '')) as documentFROM postJOIN author ON author.id = post.author_idJOIN posts_tags ON posts_tags.post_id = posts_tags.tag_idJOIN tag ON tag.id = posts_tags.tag_idGROUP BY post.id, author.id; 

 ERROR: function to_tsvector(text, text) does not exist 

 SELECT to_tsvector('simple', 'We are running'); to_tsvector ---------------------------- 'are':2 'running':3 'we':1(1 row) 

 CREATE EXTENSION unaccent;SELECT unaccent('èéê?'); unaccent---------- eeee (1 row) 

 INSERT INTO post (id, title, content, author_id, language) VALUES (4, 'il était une fois', 'il était une fois un h?tel ...', 2,'french') 

 SELECT to_tsvector(post.language, unaccent(post.title)) || to_tsvector(post.language, unaccent(post.c
                
提示：
                    本文由神整理自网络，如有侵权请联系本站删除！
                    

                    本站声明： 

                    1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持； 

                    2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
                
                

                

                                            
上一篇：使用Bucardo5实现PostgreSQL的主数据库复制_数据库其它_
                                                                
下一篇：介绍PostgreSQL中的jsonb数据类型_数据库其它_