title: postgres ANY() with BETWEEN Condition
body: <p>in case someone is wondering, i am recycling a different question i answered myself, because is realized that my problem has a different root-cause than i thought:</p>
  
  <p>My question actually seems pretty simple, but i cannot find a way.</p>
  
  <p>How do is query postgres if any element of an array is between two values?</p>
  
  <p>The Documentation states that <code>a BETWEEN b and c</code> is equivalent to <code>a &gt; b and a &lt; c</code></p>
  
  <p>This however does not work on arrays, as</p>
  
  <p><code>ANY({1, 101}) BETWEEN 10 and 20</code> has to be <strong>false</strong></p>
  
  <p>while </p>
  
  <p><code>ANY({1,101}) &gt; 10 AND ANY({1,101}) &lt; 20</code> has to be <strong>true</strong>.</p>
  
  <p>{1,101} meaning an array containing the two elements 1 and 101.</p>
  
  <p>how can i solve this problem, without resorting to workarounds?</p>
  
  <p>regards,</p>
  
  <p>BillDoor</p>
  
  <p>EDIT: for clarity:</p>
  
  <p>The scenario i have is, i am querying an xml document via xpath(), but for this problem a column containing an array of type int[] does the job.</p>
  
  <pre><code>id::int | numbers::int[]  | name::text
  1       | {1,3,200}       | Alice
  2       | {21,100}        | Bob
  </code></pre>
  
  <p>I want all Names, where there is a <code>number</code> that is between <code>20</code> and <code>30</code> - so i want <code>Bob</code></p>
  
  <p>The query </p>
  
  <pre><code>SELECT name from table where  ANY(numbers) &gt; 20 AND ANY(numbers) &lt; 30
  </code></pre>
  
  <p>will return <code>Alice</code> and <code>Bob</code>, showing that alice has numbers  > 20 as well as other numbers &lt; 30.</p>
  
  <p>A BETWEEN syntax is not allowed in this case, however between only gets mapped to > 20 AND &lt; 30 internally anyways</p>
  
  <p>Quoting the docs on the Between Operators' mapping to > and &lt; <a href="http://www.postgresql.org/docs/9.4/static/functions-comparison.html" rel="nofollow">documentation</a>:</p>
  
  <blockquote>
    <p>There is no difference between the two respective forms apart from the
    CPU cycles required to rewrite the first one into the second one
    internally.</p>
  </blockquote>
  
  <p>PS.:</p>
  
  <p>Just to avoid adding a new question for this: how can i solve</p>
  
  <pre><code>id::int | numbers::int[]  | name::text
  1       | {1,3,200}       | Alice
  2       | {21,100}        | Alicia
  
  SELECT id FROM table WHERE ANY(name) LIKE 'Alic%'
  result: 1, 2
  </code></pre>
  
  <p>i can only find examples of matching one value to multiple regex, but not matching one regex against a set of values :/. Besides the shown syntax is invalid, ANY has to be the second operand, but the second operand of LIKE has to be the regex.</p>