Postgres - Create index for full text search using 3 + columns with to_tsvector

Question

All of the examples in Postgres full text search documentation show creating an index by concatenating 2 columns, but I cannot solve how to do this with 3+ columns. Example from the Postgres docs:

to_tsvector(title || ' ' || body)

If I for example have another column that should be searched through called description, it seems that this syntax does not work as I expect:

to_tsvector(title || ' ' || body || ' ' || description)

It results in an index like this when I try it:

to_tsvector('english'::regconfig, ((("CompanyName" || ' '::text) || "Title") || ' '::text) || "Description")

There seems to be an extra set of parantheses wrapping the CompanyName and Title block.

Any ideas on what the syntax should be here? I'm having trouble finding any relevant documentation on using more columns with to_tsvector.

Closely related, on dba.SE: PostgreSQL full text search on many columns — Erwin Brandstetter
– Erwin Brandstetter, Commented Feb 7, 2019 at 5:12

Laurenz Albe · Accepted Answer · 2019-02-07 06:51:08Z

2

Your index is quite good and will be used whenever you use the same expression in a query.

Indexed expressions are not stored as text, but as a parsed tree structure. That way it doesn't break the index if you for example rename a used column.

The string concatenation operator || is left associative, so the parenthesized expression is identical to the one you originally wrote (with the exception of the configuration english that is required to make the expression IMMUTABLE, but I guess you just forgot that).

answered Feb 7, 2019 at 6:51

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

Postgres - Create index for full text search using 3 + columns with to_tsvector

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related