title: Postgresql: comparing arrays results in "malformed array literal" error
body: <p>I have a Rails app and a column that contains an array like <code>["manager", "engineer"]</code> etc. and a where statement like this:</p>
  
  <pre><code>where("? = ANY roles", query)
  </code></pre>
  
  <p>which works find if I pass a single value for query.  I want to be able to pass multiple values.  I did some Googling and an simple solution was found:</p>
  
  <pre><code>where("? &amp;&amp; roles", query )
  </code></pre>
  
  <p>except that if I pass something in like <code>"['admin', 'guest']"</code> I get this error:</p>
  
  <pre><code>PG::InvalidTextRepresentation: ERROR:  malformed array literal: "['admin', 'guest']"
  LINE 1: ....                                       $1 AND ('[''admin'...
                                                               ^
  DETAIL:  "[" must introduce explicitly-specified array dimensions.
  </code></pre>
  
  <p>I suspect that there is some weird quote escaping issue but I can't figure it out.  Those error messages results in a bunch of JSON Q&amp;As but nothing that jumps out with a solution.</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>I always seem to find a clue after posting a question - I tried:</p>
  
  <pre><code>where("'{guest, admin}'::text[] &amp;&amp; roles", query )
  </code></pre>
  
  <p>and it works - I still don't really know why but it does.  Now I can't see how to get the <code>?</code> back in there now so I can search on it.</p>
  
  <p><strong>UPDATE 2</strong></p>
  
  <p>I took the first answer below and did a bit of refactoring to get what I think is a simple elegant solution:</p>
  
  <pre><code>where("'{#{roles}}'::text[] &amp;&amp; roles")
  </code></pre>
  
  <p>This way I can pass a simple text string to my application helper that this where clause sits.  It handles single and multiple queries.</p>