What is the syntax for AND OR NOT in Postgres trigram search?

Question

I have implemented Postgres 9.6 trigram search https://www.postgresql.org/docs/9.6/static/pgtrgm.html into my application which works fine for a single search term.

I can't see how to allow my users to do AND OR NOT searches though.

Currently, if I put "perl" into the search field, it will return hundreds of results. That's great and works fine.

Now if I want to search for documents containing "perl" and also containing "javascript", no matter what search term I put in, no results come back.

I have tried for example:

"perl javascript"
"perl AND javascript"
"perl && javascript"

So I am trying to work out how I can provide to my end users a more sophisticated search than single term only. I would like my application users to be able to do full text searches with and/or/not.

Is it possible? If yes, what is the syntax?

Answer 1

SELECT *
FROM   tbl
WHERE  document ~ 'perl'
AND    document ~ 'javascript';

This query finds ...

documents containing "perl" and also containing "javascript"

Note that "perlane" or "javascripting" or "Kaperl" also qualify. To search for whole words, you might be interested in text search instead. Overview:

• Pattern matching with LIKE, SIMILAR TO or regular expressions