Postgres full text search: Multiple columns, cross table

Question

I am new to Postgres and came across full text search feature. I want to achieve the following:

• 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).

Open point: Ranking.

I understand it might not be straighfoward, but if you can provide example statements to achieve similar?

Answer 1

This is covered quite well in the PostgreSQL documentation on full-text search, which shows examples of searching multiple columns and weighting them.

See in particular controlling full-text search and 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 JOIN the tables then query them, or, for best performance, create a trigger-maintained materialized view that you can index and then query that.

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 ALL the query results then rank them, but there's no guarantee the ranking would be consistent between the two tables.