How to create postgres full text search index on multiple column

Question

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

Tim Biegeleisen · Accepted Answer · 2021-04-20 10:40:34Z

2

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;

answered Apr 20, 2021 at 10:40

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ashok Over a year ago

thank you, how we can create index for multiple columns.

Tim Biegeleisen Over a year ago

@ashok You don't...you use a workaround, such as the one my answer suggests.

ashok Over a year ago

stackoverflow.com/questions/66746383/… in this question, using || symbol we can create index has been mentioned, is it possible?

ashok Over a year ago

sorry I have updated te question, please can you check.

Fez Vrasta Over a year ago

@TimBiegeleisen why you have test1, test2, and "your search term here", I only have a single query I want to search for, not 3

Collectives™ on Stack Overflow

How to create postgres full text search index on multiple column

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related