Search for a record name that begins with a slash in Postgresql Full Text Search

by Sergio D. Márquez   Last Updated November 13, 2018 18:06 PM

I'm trying to match a DB record (operation) that contains a slash with no results.

The operation name field that I'm trying to search contains:

some /word

This is the search:

SELECT 
  "operations".* 
FROM 
  "operations" 
INNER JOIN (
  SELECT "operations"."id" FROM "operations" 
  WHERE (((
          to_tsvector(
            'simple', coalesce("operations"."name" :: text, '')
          )
        ) @@ (
          to_tsquery( 'simple', ''' ' || 'word' || ' ''' || ':*')
        )))
)

I think that psql is indexing '/word' as a file path and not including it as a result.

Is this posible on psql ?



Answers 2


I find that regex is a great help. You might look at the following page https://www.postgresql.org/docs/9.3/functions-matching.html and get the the whole word beginning with a slash.

Alpha only:

\/[A-Za-z]+

Alphanumeric:

  \/[A-Za-z0-9]+
CAtoOH
CAtoOH
November 13, 2018 19:02 PM

Using "ts_debug", you can see that you are correct, it is considering this to be part of a file path and so is indexing only as '/word' and not 'word' itself, so then searching on 'word' will not find '/word'. You could create your own parser which would emit both '/word' and 'word', or just 'word', if you wanted. But that is not exactly trivial to do.

There are probably better solutions, but it is hard to recommend one because your question is hard to understand. What kind of a db record is an "operation"? If you know you are searching for a thing with a slash, why not just include the slash in the ts_query?

jjanes
jjanes
November 16, 2018 05:05 AM

Related Questions


Performing search on a column by query

Updated May 22, 2015 22:02 PM

Fuzzy Matching with Postgresql 9.3

Updated April 06, 2017 14:06 PM