'Postgres full text search: Multiple columns, cross table

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?

postgresqlfull-text-searchtsvector


Solution 1:[1]

Following solution is good for simple forms for search across table data where one would want to search over several columns or composite columns:

SELECT * FROM table t
WHERE t.description ILIKE '%search criteria from the form' OR
      t.text ILIKE '%search criteria from the form%' OR
      t.amount ILIKE '%search criteria from the form%' OR
      CONCAT(t.description, ' ', t.text, ' ', t.amount) ILIKE '%search criteria from the form%';

So, using CONCAT function with combination hardcoded delimiters for composite column and OR to search over several columns.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Viktor Reinok

Related Questions