全文搜索

Postgres 内置了处理 Full Text Search 查询的函数。这就像 Postgres 内部的“搜索引擎”。

🌐 Postgres has built-in functions to handle Full Text Search queries. This is like a "search engine" within Postgres.

准备 #

🌐 Preparation

在这个指南中，我们将使用以下示例数据：

🌐 For this guide we'll use the following example data:

用法 #

🌐 Usage

本指南中我们将讲到的功能有：

🌐 The functions we'll cover in this guide are:

to_tsvector()#

🌐 to_tsvector() [#to-tsvector]

将你的数据转换为可搜索的标记。to_tsvector() 代表“转为文本搜索向量”。例如：

🌐 Converts your data into searchable tokens. to_tsvector() stands for "to text search vector." For example:

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

这些标记统称为“文档”，Postgres 可以用它来进行比较。

🌐 Collectively these tokens are called a "document" which Postgres can use for comparisons.

to_tsquery()#

🌐 to_tsquery() [#to-tsquery]

将查询字符串转换为匹配的标记。to_tsquery() 代表“转为文本搜索查询。”

🌐 Converts a query string into tokens to match. to_tsquery() stands for "to text search query."

这个转换步骤很重要，因为我们想要对关键词进行“模糊匹配”。 比如，如果用户搜索 eggs，而某一列的值是 egg，我们可能仍然希望返回一个匹配结果。

🌐 This conversion step is important because we will want to "fuzzy match" on keywords. For example if a user searches for eggs, and a column has the value egg, we probably still want to return a match.

Postgres 提供了几个用于创建 tsquery 对象的函数：

🌐 Postgres provides several functions to create tsquery objects:

• to_tsquery() - 需要手动指定操作符（&、|、!）
• plainto_tsquery() - 将纯文本转换为 AND 查询：plainto_tsquery('english', 'fat rats') → 'fat' & 'rat'
• phraseto_tsquery() - 创建短语查询：phraseto_tsquery('english', 'fat rats') → 'fat' <-> 'rat'
• websearch_to_tsquery() - 支持带引号、“或”和否定的网页搜索语法

比赛: @@#

🌐 Match: @@ [#match]

@@ 符号是全文搜索的“匹配”符号。它会返回 to_tsvector 结果和 to_tsquery 结果之间的任何匹配项。

🌐 The @@ symbol is the "match" symbol for Full Text Search. It returns any matches between a to_tsvector result and a to_tsquery result.

举个例子：

🌐 Take the following example:

1
select *
2
from books
3
where title = 'Harry';

上面的等号符号（=）对它匹配的内容非常“严格”。在全文本搜索的情况下，我们可能想找到所有《哈利·波特》书籍，所以我们可以重写上面的例子：

🌐 The equality symbol above (=) is very "strict" on what it matches. In a full text search context, we might want to find all "Harry Potter" books and so we can rewrite the example above:

1
select *
2
from books
3
where to_tsvector(title) @@ to_tsquery('Harry');

基本全文查询 #

🌐 Basic full text queries

搜索单列 #

🌐 Search a single column

要找到所有 books，其 description 包含单词 big：

🌐 To find all books where the description contain the word big:

1
select
2
  *
3
from
4
  books
5
where
6
  to_tsvector(description)
7
  @@ to_tsquery('big');

搜索多列 #

🌐 Search multiple columns

目前没有直接的方法可以使用 JavaScript 或 Dart 在多列中搜索，但你可以通过在数据库上创建 计算列 来实现。

🌐 Right now there is no direct way to use JavaScript or Dart to search through multiple columns but you can do it by creating computed columns on the database.

要找到所有 books，其中 description 或 title 包含单词 little：

🌐 To find all books where description or title contain the word little:

1
select
2
  *
3
from
4
  books
5
where
6
  to_tsvector(description || ' ' || title) -- concat columns, but be sure to include a space to separate them!
7
  @@ to_tsquery('little');

匹配所有搜索词 #

🌐 Match all search words

要找到所有 books，其中 description 包含 little 和 big 这两个词，我们可以使用 & 符号：

🌐 To find all books where description contains BOTH of the words little and big, we can use the & symbol:

1
select
2
  *
3
from
4
  books
5
where
6
  to_tsvector(description)
7
  @@ to_tsquery('little & big'); -- use & for AND in the search query

匹配任意搜索词 #

🌐 Match any search words

要找到所有 books，其中 description 包含任意一个单词 little 或 big，使用 | 符号：

🌐 To find all books where description contain ANY of the words little or big, use the | symbol:

1
select
2
  *
3
from
4
  books
5
where
6
  to_tsvector(description)
7
  @@ to_tsquery('little | big'); -- use | for OR in the search query

注意搜索 big 时也会包含带有 bigger（或 biggest 等）的结果。

🌐 Notice how searching for big includes results with the word bigger (or biggest, etc).

部分搜索 #

🌐 Partial search

当你想在数据中查找子字符串匹配时，部分搜索特别有用。

🌐 Partial search is particularly useful when you want to find matches on substrings within your data.

实现部分搜索 #

🌐 Implementing partial search

你可以将 :* 语法与 to_tsquery() 一起使用。这里有一个示例，用于搜索任何以“Lit”开头的书名：

🌐 You can use the :* syntax with to_tsquery(). Here's an example that searches for any book titles beginning with "Lit":

1
select title from books where to_tsvector(title) @@ to_tsquery('Lit:*');

通过 RPC 扩展功能 #

🌐 Extending functionality with RPC

要通过 API 使用部分搜索功能，你可以把搜索逻辑封装在一个数据库函数里。

🌐 To make the partial search functionality accessible through the API, you can wrap the search logic in a database function.

创建这个函数后，你可以使用你平台的 SDK 从你的应用中调用它。举个例子：

🌐 After creating this function, you can invoke it from your application using the SDK for your platform. Here's an example:

1
create or replace function search_books_by_title_prefix(prefix text)
2
returns setof books AS $$
3
begin
4
  return query
5
  select * from books where to_tsvector('english', title) @@ to_tsquery(prefix || ':*');
6
end;
7
$$ language plpgsql;

这个函数接受一个前缀参数，并返回所有标题中包含以该前缀开头的单词的书籍。在 to_tsquery() 函数中，:* 操作符用来表示前缀匹配。

🌐 This function takes a prefix parameter and returns all books where the title contains a word starting with that prefix. The :* operator is used to denote a prefix match in the to_tsquery() function.

处理查询中的空格 #

🌐 Handling spaces in queries

当你希望搜索词包含一个短语或多个单词时，你可以使用 + 作为空格的占位符来连接单词：

🌐 When you want the search term to include a phrase or multiple words, you can concatenate words using a + as a placeholder for space:

1
select * from search_books_by_title_prefix('Little+Puppy');

使用 websearch_to_tsquery()#

🌐 Web search syntax with websearch_to_tsquery() [#websearch-to-tsquery]

websearch_to_tsquery() 函数提供了一种直观的搜索语法，类似流行的网络搜索引擎，非常适合面向用户的搜索界面。

🌐 The websearch_to_tsquery() function provides an intuitive search syntax similar to popular web search engines, making it ideal for user-facing search interfaces.

基本用法 #

🌐 Basic usage

1
select *
2
from books
3
where to_tsvector(description) @@ websearch_to_tsquery('english', 'green eggs');

引用的短语 #

🌐 Quoted phrases

使用引号来搜索准确的短语：

🌐 Use quotes to search for exact phrases:

1
select * from books
2
where to_tsvector(description || ' ' || title) @@ websearch_to_tsquery('english', '"Green Eggs"');
3
-- Matches documents containing "Green" immediately followed by "Eggs"

OR 搜索 #

🌐 OR searches

使用“or”（不区分大小写）来搜索多个词：

🌐 Use "or" (case-insensitive) to search for multiple terms:

1
select * from books
2
where to_tsvector(description) @@ websearch_to_tsquery('english', 'puppy or rabbit');
3
-- Matches documents containing either "puppy" OR "rabbit"

否定 #

🌐 Negation

用破折号（-）来排除词语：

🌐 Use a dash (-) to exclude terms:

1
select * from books
2
where to_tsvector(description) @@ websearch_to_tsquery('english', 'animal -rabbit');
3
-- Matches documents containing "animal" but NOT "rabbit"

复杂查询 #

🌐 Complex queries

结合多个操作符进行高级搜索：

🌐 Combine multiple operators for sophisticated searches:

1
select * from books
2
where to_tsvector(description || ' ' || title) @@
3
  websearch_to_tsquery('english', '"Harry Potter" or "Dr. Seuss" -vegetables');
4
-- Matches books by "Harry Potter" or "Dr. Seuss" but excludes those mentioning vegetables

创建索引 #

🌐 Creating indexes

既然你已经让全文搜索工作了，现在创建一个 index。这允许 Postgres 预先“构建”文档，这样在执行查询时就不需要再创建它们。这会让我们的查询快得多。

🌐 Now that you have Full Text Search working, create an index. This allows Postgres to "build" the documents preemptively so that they don't need to be created at the time we execute the query. This will make our queries much faster.

可搜索的列 #

🌐 Searchable columns

在 books 表中创建一个新列 fts 来存储 title 和 description 列的可搜索索引。

🌐 Create a new column fts inside the books table to store the searchable index of the title and description columns.

我们可以使用 Postgres 的一个特殊功能，叫做 生成列，来确保每当 title 和 description 列的值发生变化时，索引都会被更新。

🌐 We can use a special feature of Postgres called Generated Columns to ensure that the index is updated any time the values in the title and description columns change.

1
alter table
2
  books
3
add column
4
  fts tsvector generated always as (to_tsvector('english', description || ' ' || title)) stored;
5
6
create index books_fts on books using gin (fts); -- generate the index
7
8
select id, fts
9
from books;

使用新列搜索 #

🌐 Search using the new column

既然我们已经创建并填充了索引，现在我们可以像以前一样使用相同的技巧来搜索它：

🌐 Now that we've created and populated our index, we can search it using the same techniques as before:

1
select
2
  *
3
from
4
  books
5
where
6
  fts @@ to_tsquery('little & big');

查询操作符 #

🌐 Query operators

访问 Postgres: Text Search Functions and Operators 来了解更多你可以使用的查询操作符，以进行更高级的 full text queries，例如：

🌐 Visit Postgres: Text Search Functions and Operators to learn about additional query operators you can use to do more advanced full text queries, such as:

接近度：<->#

🌐 Proximity: <-> [#proximity]

接近符号对于查找相隔一定“距离”的词语很有用。 例如，要查找短语 big dreams，其中“big”的匹配项紧接着“dreams”的匹配项：

🌐 The proximity symbol is useful for searching for terms that are a certain "distance" apart. For example, to find the phrase big dreams, where the a match for "big" is followed immediately by a match for "dreams":

1
select
2
  *
3
from
4
  books
5
where
6
  to_tsvector(description) @@ to_tsquery('big <-> dreams');

我们也可以使用 <-> 来查找彼此距离一定范围内的词。例如，要查找 year 和 school 相隔不超过 2 个词的情况：

🌐 We can also use the <-> to find words within a certain distance of each other. For example to find year and school within 2 words of each other:

1
select
2
  *
3
from
4
  books
5
where
6
  to_tsvector(description) @@ to_tsquery('year <2> school');

否定：!#

🌐 Negation: ! [#negation]

否定符号可以用来查找不包含某个搜索词的短语。例如，要查找包含 big 但不包含 little 的记录：

🌐 The negation symbol can be used to find phrases which don't contain a search term. For example, to find records that have the word big but not little:

1
select
2
  *
3
from
4
  books
5
where
6
  to_tsvector(description) @@ to_tsquery('big & !little');

搜索结果排名 #

🌐 Ranking search results [#ranking]

Postgres 提供了排名函数，可以按相关性对搜索结果进行排序，帮助你先展示最相关的匹配项。由于排名函数需要在服务器端计算，所以使用 RPC 函数和生成列吧。

🌐 Postgres provides ranking functions to sort search results by relevance, helping you present the most relevant matches first. Since ranking functions need to be computed server-side, use RPC functions and generated columns.

创建一个带排名的搜索功能 #

🌐 Creating a search function with ranking [#search-function-ranking]

首先，创建一个处理搜索和排名的 Postgres 函数：

🌐 First, create a Postgres function that handles search and ranking:

1
create or replace function search_books(search_query text)
2
returns table(id int, title text, description text, rank real) as $$
3
begin
4
  return query
5
  select
6
    books.id,
7
    books.title,
8
    books.description,
9
    ts_rank(to_tsvector('english', books.description), to_tsquery(search_query)) as rank
10
  from books
11
  where to_tsvector('english', books.description) @@ to_tsquery(search_query)
12
  order by rank desc;
13
end;
14
$$ language plpgsql;

现在你可以从客户端调用这个函数了：

🌐 Now you can call this function from your client:

1
const { data, error } = await supabase.rpc('search_books', { search_query: 'big' })

带权重列的排名 #

🌐 Ranking with weighted columns [#weighted-ranking]

Postgres 允许你使用权重标签给文档的不同部分分配不同的重要性。这在你希望某些字段（比如标题）的匹配比其他字段（比如描述）的匹配排名更高时尤其有用。

🌐 Postgres allows you to assign different importance levels to different parts of your documents using weight labels. This is especially useful when you want matches in certain fields (like titles) to rank higher than matches in other fields (like descriptions).

了解重量标签 #

🌐 Understanding weight labels

Postgres 使用四个权重标签：A、B、C 和 D，其中：

🌐 Postgres uses four weight labels: A, B, C, and D, where:

• A = 最高重要性（权重1.0）
• B = 高重要性（权重 0.4）
• C = 中等重要性（权重0.2）
• D = 低重要性（权重0.1）

创建加权搜索列 #

🌐 Creating weighted search columns

首先，创建一个加权的 tsvector 列，让标题比描述有更高的优先级：

🌐 First, create a weighted tsvector column that gives titles higher priority than descriptions:

1
-- Add a weighted fts column
2
alter table books
3
add column fts_weighted tsvector
4
generated always as (
5
  setweight(to_tsvector('english', title), 'A') ||
6
  setweight(to_tsvector('english', description), 'B')
7
) stored;
8
9
-- Create index for the weighted column
10
create index books_fts_weighted on books using gin (fts_weighted);

现在创建一个使用这个加权列的搜索功能：

🌐 Now create a search function that uses this weighted column:

1
create or replace function search_books_weighted(search_query text)
2
returns table(id int, title text, description text, rank real) as $$
3
begin
4
  return query
5
  select
6
    books.id,
7
    books.title,
8
    books.description,
9
    ts_rank(books.fts_weighted, to_tsquery(search_query)) as rank
10
  from books
11
  where books.fts_weighted @@ to_tsquery(search_query)
12
  order by rank desc;
13
end;
14
$$ language plpgsql;

自定义权重数组 #

🌐 Custom weight arrays

你也可以通过向 ts_rank() 提供一个权重数组来指定自定义权重：

🌐 You can also specify custom weights by providing a weight array to ts_rank():

1
create or replace function search_books_custom_weights(search_query text)
2
returns table(id int, title text, description text, rank real) as $$
3
begin
4
  return query
5
  select
6
    books.id,
7
    books.title,
8
    books.description,
9
    ts_rank(
10
      '{0.0, 0.2, 0.5, 1.0}'::real[], -- Custom weights {D, C, B, A}
11
      books.fts_weighted,
12
      to_tsquery(search_query)
13
    ) as rank
14
  from books
15
  where books.fts_weighted @@ to_tsquery(search_query)
16
  order by rank desc;
17
end;
18
$$ language plpgsql;

这个例子使用了自定义权重，其中：

🌐 This example uses custom weights where:

• A 标记的术语（标题）具有最大权重（1.0）
• B 标记的术语（描述）有中等权重（0.5）
• C 标记的术语权重低（0.2）
• 被标记为 D 的术语会被忽略（0.0）

使用加权搜索 #

🌐 Using the weighted search

1
// Search with standard weighted ranking
2
const { data, error } = await supabase.rpc('search_books_weighted', { search_query: 'Harry' })
3
4
// Search with custom weights
5
const { data: customData, error: customError } = await supabase.rpc('search_books_custom_weights', {
6
  search_query: 'Harry',
7
})

带结果的实际例子 #

🌐 Practical example with results

假设你搜索“Harry”。使用加权列时：

🌐 Say you search for "Harry". With weighted columns:

1. 《哈利·波特与火焰杯》（标题匹配）权重 A = 1.0
2. 描述中提到“Harry”的书 权重 B = 0.4

这确保了标题中包含“Harry”的书籍排名明显高于仅在描述中提到“Harry”的书籍，为用户提供更相关的搜索结果。

🌐 This ensures that books with "Harry" in the title ranks significantly higher than books that only mention "Harry" in the description, providing more relevant search results for users.

使用带索引的排名 #

🌐 Using ranking with indexes [#ranking-with-indexes]

当使用你之前创建的 fts 列时，排序会更高效。创建一个使用索引列的函数：

🌐 When using the fts column you created earlier, ranking becomes more efficient. Create a function that uses the indexed column:

1
create or replace function search_books_fts(search_query text)
2
returns table(id int, title text, description text, rank real) as $$
3
begin
4
  return query
5
  select
6
    books.id,
7
    books.title,
8
    books.description,
9
    ts_rank(books.fts, to_tsquery(search_query)) as rank
10
  from books
11
  where books.fts @@ to_tsquery(search_query)
12
  order by rank desc;
13
end;
14
$$ language plpgsql;

1
const { data, error } = await supabase.rpc('search_books_fts', { search_query: 'little & big' })

使用带排名的网络搜索语法 #

🌐 Using web search syntax with ranking [#websearch-ranking]

你也可以创建一个函数，将 websearch_to_tsquery() 与排名结合起来，以便用户更容易搜索：

🌐 You can also create a function that combines websearch_to_tsquery() with ranking for user-friendly search:

1
create or replace function websearch_books(search_text text)
2
returns table(id int, title text, description text, rank real) as $$
3
begin
4
  return query
5
  select
6
    books.id,
7
    books.title,
8
    books.description,
9
    ts_rank(books.fts, websearch_to_tsquery('english', search_text)) as rank
10
  from books
11
  where books.fts @@ websearch_to_tsquery('english', search_text)
12
  order by rank desc;
13
end;
14
$$ language plpgsql;

1
// Support natural search syntax
2
const { data, error } = await supabase.rpc('websearch_books', {
3
  search_text: '"little puppy" or train -vegetables',
4
})

资源 #

🌐 Resources

• Postgres：文本搜索函数和操作符