【SQL】PostgreSQLで全文検索をする

全文検索とは

全文検索(または単にテキスト検索)は、
問い合わせを満たす自然言語の文書を識別し、更には問い合わせとの関連性の順に並び替えることができます。
公式より

...意味わからんすね😊

もっとも一般的な検索は、与えられた検索語を含む文書を探し、問い合わせとの類似性の順に返す、というものです。
公式より

要するに、全文検索とは文字列を検索できることだと認識しています。

SQLの実行方法

導入version: PostgreSQL 8.3〜

db=# select * from article;
 title  |     tag
--------+-------------
 title1 | 001 002 003
 title2 | 002 003 004
 title3 | 003 004 005
 title4 | 004 005 006
 title5 | 005 006 007

tagに'002'を持っているレコードのみを取得したい場合...

db=# select * from article where tag::tsvector @@ '002'::tsquery = 't';
 title  |     tag
--------+-------------
 title1 | 001 002 003
 title2 | 002 003 004

tagに'001'かつ'002'を持っているレコードのみを取得したい場合...

db=# select * from article where tag::tsvector @@ '001 & 002' ::tsquery = 't';
 title  |     tag
--------+-------------
 title1 | 001 002 003

解説

tsvector（文書）とtsquery（問い合わせ）を照合演算子@@で比較して、一致した場合、't'つまりtrueが返ってくるという仕組みです。

つまり...

'001 002 003'::tsvector @@ '002'::tsquery = 't'

となり、'002'というテキスト（文書）が問い合わせと一致するため、trueとなります。

※1 文書 ･･･ 全文検索システムにおける検索の単位です。

ちなみに

もちろん、マルチバイト文字を検索することもできます。

db=# select * from book where tag::tsvector @@ '猫'::tsquery = 't';
     title      |            tag
----------------+----------------------------
 吾輩は猫である   | 夏目漱石 日本 小説 猫 明治