Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

120
Views
Should I be stripping colons from strings before creating a tsquery?

We are using websearch_to_tsquery() in our app. I've noticed that when passed a "quoted" string to find an exact match, it will interpret a colon character ":" as a word, while to_tsvector does not. This prevents a tsquery from matching a tsvector created from the same string. The behavior exists in at least Postgres 12 and 13. Is this expected?

With a colon:

SELECT websearch_to_tsquery('english', '"Design Patterns in C++: Behavioral - Observer to Visitor"')
'design' <-> 'pattern' <2> 'c' <2> 'behavior' <-> 'observ' <2> 'visitor'

Now with the colon removed:

SELECT websearch_to_tsquery('english', '"Design Patterns in C++ Behavioral - Observer to Visitor"')
'design' <-> 'pattern' <2> 'c' <-> 'behavior' <-> 'observ' <2> 'visitor'

Note the 'c' <2> 'behavior' vs 'c' <-> 'behavior'. A tsvector created from the same string with a colon does not include a colon as a word. That is what I would expect, but this means the tsquery will not match a tsvector created from the same string.

SELECT to_tsvector('english', '"Design Patterns in C++: Behavioral - Observer to Visitor"')
'behavior':5 'c':4 'design':1 'observ':6 'pattern':2 'visitor':8

Documentation for websearch_to_tsquery:

Converts text to a tsquery, normalizing words according to the specified or default configuration. Quoted word sequences are converted to phrase tests. The word “or” is understood as producing an OR operator, and a dash produces a NOT operator; other punctuation is ignored. This approximates the behavior of some common web search tools.

Should I be stripping colons from strings before creating a tsquery? Are there other characters that characters that need to be removed?

over 4 years ago · Santiago Trujillo
1 answers
Answer question

0

It looks like this is a bug that will be fixed in v14, but the change was deemed to invasive to backpatch into older releases, because it depends on some other changes which were not getting backpatched.

https://postgr.es/m/16592-70b110ff9731c07d@postgresql.org https://postgr.es/m/CAPpHfdv0EzVhf6CWfB1_TTZqXV_2Sn-jSY3zSd7ePH%3D-%2B1V2DQ%40mail.gmail.com

over 4 years ago · Santiago Trujillo Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!