Supabaseで作ったPostgreSQLのテーブルに全文検索を実行する

PostgreSQLなので、Supabaseで作ったデータベースに対して全文検索をかけることができる（らしい）です。SQLまわりは苦手・・・とも言ってられないので、体当たりで触ってみた覚書です。

検索するためのテーブルとデータを用意する

方法を紹介しているドキュメントを参考にして、検索するためのテーブルやデータを投入します。

create table books (
  id serial primary key,
  title text,
  author text,
  description text
);

insert into books
  (title, author, description)
values
  (
    'The Poky Little Puppy',
    'Janette Sebring Lowrey',
    'Puppy is slower than other, bigger animals.'
  ),
  ('The Tale of Peter Rabbit', 'Beatrix Potter', 'Rabbit eats some vegetables.'),
  ('Tootle', 'Gertrude Crampton', 'Little toy train has big dreams.'),
  (
    'Green Eggs and Ham',
    'Dr. Seuss',
    'Sam has changing food preferences and eats unusually colored food.'
  ),
  (
    'Harry Potter and the Goblet of Fire',
    'J.K. Rowling',
    'Fourth year of school starts, big drama ensues.'
  );

to_tsvectorでベクトル変換、to_tsqueryを組みわせて全文検索する

tsはText Searchの略語でしょうか。とりあえずこの2つのPostgreSQLに組み込まれた関数を利用します。

select to_tsvector('green eggs and ham');
-- Returns 'egg':2 'green':1 'ham':4

@@でつなぐような書き方をしていました。

select *
from books
where to_tsvector(title) @@ to_tsquery('Harry')

実行した結果がこちら。Harryと関連するレコードだけが取れています。

日本語での検索にもデフォルトで対応しているか試してみた

この手の検索は日本語などのマルチバイト系にデフォルトで対応していないことが多いため、検証してみます。投入する書籍の選定理由は「とあるアニメをさっきまで観ていたから」だけです。（説明文はWikipediaのテキストを引用）

insert into books
  (title, author, description)
values
  (
    '人間失格',
    '太宰治',
    '太宰治による中編小説。『ヴィヨンの妻』『走れメロス』『斜陽』に並ぶ太宰の代表作の1つである。'
  );

SQL Editorで試したところ、やはりうまく動きませんでした。

通常のSELECT ~ WHERE ~ では取得できたので、tsXXX系がマルチバイトに対応していない可能性が高そうです。

おそらくですが、日本語に対応させるためのカスタマイズを施す必要がありそうです。この辺りはもう少し調べてみて、上手くいったものがあれば後日紹介します。

参考にした記事など

• https://supabase.com/docs/guides/database/full-text-search
• https://www.amris.jp/tsja/index.html