CODEWITHSUNDEEP
sqlpostgresql
ashok
ashok

Reputation: 1268

How to create postgres full text search index on multiple column

I'm migrating from sql server to postgres. IN sql server I have created the index like this:

 CREATE FULLTEXT INDEX ON [testdb].[dbo].['table'] (test1,test2...) KEY INDEX unique_index
              WITH STOPLIST = SYSTEM

How can I created the same thing in postgres, if I'm trying to create for multiple column like this it is giving error:

CREATE INDEX ON [testdb].[dbo].['table'] USING gin (to_tsvector('english', test1)||to_tsvector('english',test2)||to_tsvector('english', test3));

Error:

ERROR: function to_tsvector(unknown, character varying, character varying) does not exist

Upvotes: 1

Views: 2024

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

In Postgres, the to_tsvector function only accepts a single column as input. But this does not mean that we cannot do a full text search using multiple columns. For instance, assuming you wanted to query using test1 and test2, rank the results, and then retrieve the top 10 matches, you could use this query:

SELECT *,
    ts_rank(
        to_tsvector('english', test1) || to_tsvector('english', test2),
        to_tsquery('english', 'your search term here')
    ) AS rnk
FROM yourTable
WHERE
    to_tsvector('english', test1) || to_tsvector('english', test2) @@
    to_tsquery('english', 'your search term here')
ORDER BY
    rnk DESC
LIMIT 10;

Upvotes: 2

Related Questions