Postgres Phrase Search

Tie fighter operator #

One of the new features in Postgres 9.6 is the ability to do phrase text
searching. There has always been full text search available in previous
versions, but if you wanted to look for specific phrases, that required more
of an involved query. But now with version 9.6, we are able to search for
phrases where the words are grouped together. Say if we had the text My weekly summary text, previous versions could allow you to search for weekly summary and find it, but then you might also find a text that had weekly
and summary separated by 100 words.

With 9.6, there is a 'tie fighter' operator <-> that allows for specifying
the number of words in between. This allows for grouping of words, known as
phrases.

Test table looking like:

    postgres=# select * from test;
     id |             sample_text
    ----+-------------------------------------
      1 | test string number 1
      2 | test daily string number 2
      3 | test weekly string number 3
      4 | nothing to be worried about
      5 | something not to be me caring about

9.4 #

    postgres=# select * from test where sample_text::tsvector @@ 'test & number'::tsquery;
     id |         sample_text
    ----+-----------------------------
      1 | test string number 1
      2 | test daily string number 2
      3 | test weekly string number 3

This shows searching for text test and number within the table.

9.6 #

    postgres=# select * from test where sample_text @@ to_tsquery('test & number') ;
     id |         sample_text
    ----+-----------------------------
      1 | test string number 1
      2 | test daily string number 2
      3 | test weekly string number 3
    (3 rows)
    
    postgres=# select * from test where sample_text @@ phraseto_tsquery('test number');
     id | sample_text
    ----+-------------
    (0 rows)
    
    postgres=# select * from test where sample_text @@ to_tsquery('test <2> number') ;
     id |     sample_text
    ----+----------------------
      1 | test string number 1
    (1 row)
    
    postgres=# select * from test where sample_text @@ to_tsquery('test <3> number') ;
     id |         sample_text
    ----+-----------------------------
      2 | test daily string number 2
      3 | test weekly string number 3
    (2 rows)

Here you can see the tie fighter operator in use, being able to specify the
number of words between the two specified words.

That's a very quick and short summary of phrase searching for now, I haven't
been using it extensively yet but there is also tsvector editing functions
to fine tune editing features.

← Home