title: Hibernate, Postgres & Array Type
body: <p>I'm stuck on a specific issue using <strong><a href="http://www.postgresql.org/docs/9.3/static/functions-array.html">array type</a></strong> in postgresql 9.3 mapped with hibernate 4.1.0. 
  This type allows me to have really strong data model, without building lots of tables and joins.</p>
  
  <p>In order to map a field stored with this particular type, I have used a <a href="http://finethinking.wordpress.com/2011/10/27/hibernate-arrays-and-postgres/">UserType</a></p>
  
  <p>Anyway, it works well with pure hibernate (hql) but I need also to send <strong>sql native query</strong> to my database. When I do it, in spite of many tries, I have not found any way to do that. </p>
  
  <p>I try many syntaxes based on this</p>
  
  <pre><code>String[] values = {"value1", "value2"};
  String queryString = "SELECT * FROM instances WHERE values &amp;&amp; :values";
  Query query = this.getSession().createSQLQuery(queryString).addEntity(Instance.class);
  query.setParameterList("values", values);
  query.list();
  </code></pre>
  
  <p>I got
    Operator does not exists : text[] &amp;&amp; character varying</p>
  
  <p>It should give following syntax in jdbc : ['value1', 'value2'] and it seems to give 'value1'...</p>
  
  <p>I tried many syntaxes
  with</p>
  
  <ul>
  <li>Collection </li>
  <li>Pure Arrays</li>
  <li>[ :values ] syntax :  I got 
  Syntax error near "["</li>
  </ul>
  
  <hr>
  
  <p>I need to send native query because I use <a href="http://wiki.postgresql.org/wiki/Materialized_Views">Materialized View</a> for performance gains.</p>
  
  <p>My SQL Query works in postgresql console. So it is an hibernate specific issue.</p>