title: Making full text search with Postgresql and hibernate - missing type for tsvector and operator @@
body: <p>I have postgresql database where I'd like to run full text search queries by using JPA/Hibernate criteria API. My issue is that I don't know which type should I use for tsvector type and what is a replacement for @@ operator?</p>
  
  <p>I created a database view which combines data from two tables and makes a concatenated tsvector:</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE OR REPLACE VIEW node_name_description_tags AS
      SELECT nodeId, document
      FROM (SELECT node.id as nodeId,
                   to_tsvector('english', node.name) || 
                   to_tsvector('english', coalesce(node.description, ' ')) ||
                   to_tsvector('english', coalesce(string_agg(tag.name, ' '), ' '))  as document
            FROM node
            JOIN tag_node ON node.id = tag_node.node_id
            JOIN tag ON tag.id = tag_node.tag_id
            GROUP BY nodeId) as documents                  
  </code></pre>
  
  <p>Then I can run queries on it like this and it returns what I expect:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT * FROM node_name_description_tags WHERE document @@ PLAINTO_TSQUERY('english', 'integration user administration file')
  </code></pre>
  
  <p>What I was going to do next - create hibernate entity mapped to this view, but I don't know which type to use for column tsvector. Then I was going to to create a hibernate specification with where clause, but I don't know how operator @@ is implemented in hibernate. It seems that this functionality isn't supported at all!</p>
  
  <p>I found on the internet that people propose to use custom dialect with added full-text-search function which generates @@ where clause. That's, basically, everything what I have now.</p>
  
  <p>Any advice on how to make this working from Hibernate?</p>