postgresql Postgres全文搜索：多列、交叉表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13256645/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres full text search: Multiple columns, cross table
提问by Damandeep Thakur
I am new to Postgres and came across full text search feature. I want to achieve the following:
我是 Postgres 的新手，遇到了全文搜索功能。我想实现以下目标：
- Specify some table and fields to search on.
- When the user search for some text, it should be searched on above specified table fields.
- 指定要搜索的一些表和字段。
- 当用户搜索某些文本时，应该在上面指定的表格字段中进行搜索。
e.g.
例如
CREATE TABLE customer (name text)
CREATE TABLE address (city text)
Search for 'Ram' should find both name 'Ram*' and city 'Ram*' (may be max 10 records).
搜索“Ram”应该同时找到名称“Ram*”和城市“Ram*”（最多可能有 10 条记录）。
Open point: Ranking.
开放点：排名。
I understand it might not be straighfoward, but if you can provide example statements to achieve similar?
我知道这可能不是直截了当的，但是您是否可以提供示例语句来实现类似的功能？
回答by Craig Ringer
This is covered quite well in the PostgreSQL documentation on full-text search, which shows examples of searching multiple columns and weighting them.
关于全文搜索的PostgreSQL 文档对此进行了很好的介绍，其中显示了搜索多个列并对其进行加权的示例。
See in particular controlling full-text searchand manipulating documents. Ranking is also right there, in ranking search results.
请特别参阅控制全文搜索和操作文档。排名也在那里，在排名搜索结果中。
You haven't really provided enough information to say much more. For searching across multiple tables you usually want to JOINthe tables then query them, or, for best performance, create a trigger-maintained materialized view that you can index and then query that.
你还没有真正提供足够的信息来说更多。对于跨多个表的搜索，您通常希望对JOIN这些表进行查询，或者，为了获得最佳性能，创建一个触发器维护的物化视图，您可以对其进行索引然后查询。
If the tables are independent and unrelated it doesn't make tons of sense to query them in a single query; you'd usually use multiple tsearch2 queries for this. I guess it's possible that you could UNION ALLthe query results then rank them, but there's no guarantee the ranking would be consistent between the two tables.
如果表是独立且不相关的，则在单个查询中查询它们没有意义；您通常会为此使用多个 tsearch2 查询。我想您可以UNION ALL对查询结果进行排序，然后对它们进行排名，但不能保证两个表之间的排名是一致的。
