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"."id" FROM "operations" 
  WHERE (((
            '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 and get the the whole word beginning with a slash.

Alpha only:



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?

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