PostgreSQLの全文検索機能
最近、「PostgreSQLの全文検索機能を試してみる」という記事がバズっていました。
しかし、内容がPostgreSQL初心者には難しく、Goの話も含まれていて理解しにくい方も多いと思うので再解釈してみました。
まず、以下の全文検索を試すためにPostgreSQLを試せるPlaygroundのWebページで以下のSQL文を実行してダミーのテーブルのデータを作成します。
https://aiven.io/tools/pg-playground
SQL
CREATE TABLE IF NOT EXISTS test (
id SERIAL PRIMARY KEY,
name TEXT,
age INT
);
CREATE TABLE IF NOT EXISTS test2 (
id SERIAL PRIMARY KEY,
name TEXT,
age INT
);
INSERT INTO test (name, age) VALUES
('佐藤', 28),
('鈴木', 30),
('山田', 26);
INSERT INTO test2 (name, age) VALUES
('山本', 29),
('中村', 31),
('小林', 26);テーブルが追加されたら、画面右のTablesにtable, table2が表示されます。
次に「WITH t AS ()」でテーブルをまとめて、最後にWHEREで以下の形式で全文検索を実行します。
例えば「山田」を検索する場合は以下のようになり、実行すると「山田」のあるtable_name, id, name, ageが表示されます。
SQL
WITH t AS (
SELECT 'test' AS table_name, * FROM test
UNION ALL
SELECT 'test2' AS table_name, * FROM test2
)
SELECT *
FROM t
WHERE to_tsvector(name || ' ' || age::text) @@ to_tsquery('山田');| table_name | id | name | age |
| test | 3 | 山田 | 26 |
ageは数値型なので、age::text にキャストして文字列として検索します。
SQL
WITH t AS (
SELECT 'test' AS table_name, * FROM test
UNION ALL
SELECT 'test2' AS table_name, * FROM test2
)
SELECT *
FROM t
WHERE to_tsvector(name || ' ' || age::text) @@ to_tsquery('28');| table_name | id | name | age |
| test | 1 | 佐藤 | 28 |
テーブルが3つある場合はUNION ALLで結合して以下のようになります。
SQL
WITH t AS (
SELECT 'test' AS table_name, * FROM test
UNION ALL
SELECT 'test2' AS table_name, * FROM test2
UNION ALL
SELECT 'test3' AS table_name, * FROM test3
)
SELECT *
FROM t
WHERE to_tsvector(name || ' ' || age::text) @@ to_tsquery('山田');テキストは完全一致のため、to_tsquery('山') だと検出されません。
SQL
WITH t AS (
SELECT 'test' AS table_name, * FROM test
UNION ALL
SELECT 'test2' AS table_name, * FROM test2
)
SELECT *
FROM t
WHERE to_tsvector(name || ' ' || age::text) @@ to_tsquery('山');前方一致の場合はto_tsquery('山:*')で検索できます。
SQL
WITH t AS (
SELECT 'test' AS table_name, * FROM test
UNION ALL
SELECT 'test2' AS table_name, * FROM test2
)
SELECT *
FROM t
WHERE to_tsvector(name || ' ' || age::text) @@ to_tsquery('山:*');to_tsqueryで部分一致はできないので、部分一致検索はLIKEを使用してください。
SQL
WITH t AS (
SELECT 'test' AS table_name, * FROM test
UNION ALL
SELECT 'test2' AS table_name, * FROM test2
)
SELECT *
FROM t
WHERE name LIKE '%山%' OR age::text LIKE '%山%';| table_name | id | name | age |
| test | 3 | 山田 | 26 |
| test2 | 1 | 山本 | 29 |
