Home Explore Blog CI



postgresql

4th chunk of `doc/src/sgml/intarray.sgml`
71de99ebb3da545744ce74fe80aabdce8233f83b36f9fefe0000000100000f70
 role="func_table_entry"><para role="func_signature">
        <type>integer[]</type> <literal>+</literal> <type>integer</type>
        <returnvalue>integer[]</returnvalue>
       </para>
       <para>
        Adds element to end of array.
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>integer[]</type> <literal>+</literal> <type>integer[]</type>
        <returnvalue>integer[]</returnvalue>
       </para>
       <para>
        Concatenates the arrays.
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>integer[]</type> <literal>-</literal> <type>integer</type>
        <returnvalue>integer[]</returnvalue>
       </para>
       <para>
        Removes entries matching the right argument from the array.
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>integer[]</type> <literal>-</literal> <type>integer[]</type>
        <returnvalue>integer[]</returnvalue>
       </para>
       <para>
        Removes elements of the right array from the left array.
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>integer[]</type> <literal>|</literal> <type>integer</type>
        <returnvalue>integer[]</returnvalue>
       </para>
       <para>
        Computes the union of the arguments.
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>integer[]</type> <literal>|</literal> <type>integer[]</type>
        <returnvalue>integer[]</returnvalue>
       </para>
       <para>
        Computes the union of the arguments.
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>integer[]</type> <literal>&amp;</literal> <type>integer[]</type>
        <returnvalue>integer[]</returnvalue>
       </para>
       <para>
        Computes the intersection of the arguments.
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>integer[]</type> <literal>@@</literal> <type>query_int</type>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Does array satisfy query?  (see below)
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>query_int</type> <literal>~~</literal> <type>integer[]</type>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Does array satisfy query?  (commutator of <literal>@@</literal>)
       </para></entry>
      </row>
    </tbody>
   </tgroup>
  </table>

  <para>
   The operators <literal>&amp;&amp;</literal>, <literal>@&gt;</literal> and
   <literal>&lt;@</literal> are equivalent to <productname>PostgreSQL</productname>'s built-in
   operators of the same names, except that they work only on integer arrays
   that do not contain nulls, while the built-in operators work for any array
   type.  This restriction makes them faster than the built-in operators
   in many cases.
  </para>

  <para>
   The <literal>@@</literal> and <literal>~~</literal> operators test whether an array
   satisfies a <firstterm>query</firstterm>, which is expressed as a value of a
   specialized data type <type>query_int</type>.  A <firstterm>query</firstterm>
   consists of integer values that are checked against the elements of
   the array, possibly combined using the operators <literal>&amp;</literal>
   (AND), <literal>|</literal> (OR), and <literal>!</literal> (NOT).  Parentheses
   can be used as needed.  For example,
   the query <literal>1&amp;(2|3)</literal> matches arrays that contain 1
   and also contain either 2 or 3.
  </para>
 </sect2>

Title: Integer Array Operators in PostgreSQL
Summary
This section details various operators for integer arrays in PostgreSQL, specifically from the intarray module. It includes operators for array manipulation such as addition (+), removal (-), union (|), and intersection (&). The operators work with both single integers and integer arrays. Special operators like '@@' and '~~' are introduced to test if an array satisfies a query expressed in query_int type. The document also mentions that some operators (&&, @>, <@) are faster versions of built-in PostgreSQL operators, optimized for non-null integer arrays. The query syntax for '@@' and '~~' operators is briefly explained, allowing for complex conditions using AND (&), OR (|), and NOT (!) operators.