doc/src/sgml/intarray.sgml

   1 <!-- $PostgreSQL$ -->
   2
   3 <sect1 id="intarray">
   4  <title>intarray</title>
   5
   6  <indexterm zone="intarray">
   7   <primary>intarray</primary>
   8  </indexterm>
   9
  10  <para>
  11   The <filename>intarray</> module provides a number of useful functions
  12   and operators for manipulating one-dimensional arrays of integers.
  13   There is also support for indexed searches using some of the operators.
  14  </para>
  15
  16  <sect2>
  17   <title><filename>intarray</> Functions and Operators</title>
  18
  19   <table id="intarray-func-table">
  20    <title><filename>intarray</> Functions</title>
  21
  22    <tgroup cols="5">
  23     <thead>
  24      <row>
  25       <entry>Function</entry>
  26       <entry>Return Type</entry>
  27       <entry>Description</entry>
  28       <entry>Example</entry>
  29       <entry>Result</entry>
  30      </row>
  31     </thead>
  32
  33     <tbody>
  34      <row>
  35       <entry><function>icount(int[])</function></entry>
  36       <entry><type>int</type></entry>
  37       <entry>number of elements in array</entry>
  38       <entry><literal>icount('{1,2,3}'::int[])</literal></entry>
  39       <entry><literal>3</literal></entry>
  40      </row>
  41
  42      <row>
  43       <entry><function>sort(int[], text dir)</function></entry>
  44       <entry><type>int[]</type></entry>
  45       <entry>sort array &mdash; <parameter>dir</> must be <literal>asc</> or <literal>desc</></entry>
  46       <entry><literal>sort('{1,2,3}'::int[], 'desc')</literal></entry>
  47       <entry><literal>{3,2,1}</literal></entry>
  48      </row>
  49
  50      <row>
  51       <entry><function>sort(int[])</function></entry>
  52       <entry><type>int[]</type></entry>
  53       <entry>sort in ascending order</entry>
  54       <entry><literal>sort(array[11,77,44])</literal></entry>
  55       <entry><literal>{11,44,77}</literal></entry>
  56      </row>
  57
  58      <row>
  59       <entry><function>sort_asc(int[])</function></entry>
  60       <entry><type>int[]</type></entry>
  61       <entry>sort in ascending order</entry>
  62       <entry><literal></literal></entry>
  63       <entry><literal></literal></entry>
  64      </row>
  65
  66      <row>
  67       <entry><function>sort_desc(int[])</function></entry>
  68       <entry><type>int[]</type></entry>
  69       <entry>sort in descending order</entry>
  70       <entry><literal></literal></entry>
  71       <entry><literal></literal></entry>
  72      </row>
  73
  74      <row>
  75       <entry><function>uniq(int[])</function></entry>
  76       <entry><type>int[]</type></entry>
  77       <entry>remove adjacent duplicates</entry>
  78       <entry><literal>uniq(sort('{1,2,3,2,1}'::int[]))</literal></entry>
  79       <entry><literal>{1,2,3}</literal></entry>
  80      </row>
  81
  82      <row>
  83       <entry><function>idx(int[], int item)</function></entry>
  84       <entry><type>int</type></entry>
  85       <entry>index of first element matching <parameter>item</> (0 if none)</entry>
  86       <entry><literal>idx(array[11,22,33,22,11], 22)</literal></entry>
  87       <entry><literal>2</literal></entry>
  88      </row>
  89
  90      <row>
  91       <entry><function>subarray(int[], int start, int len)</function></entry>
  92       <entry><type>int[]</type></entry>
  93       <entry>portion of array starting at position <parameter>start</>, len <parameter>elements</></entry>
  94       <entry><literal>subarray('{1,2,3,2,1}'::int[], 2, 3)</literal></entry>
  95       <entry><literal>{2,3,2}</literal></entry>
  96      </row>
  97
  98      <row>
  99       <entry><function>subarray(int[], int start)</function></entry>
 100       <entry><type>int[]</type></entry>
 101       <entry>portion of array starting at position <parameter>start</></entry>
 102       <entry><literal>subarray('{1,2,3,2,1}'::int[], 2)</literal></entry>
 103       <entry><literal>{2,3,2,1}</literal></entry>
 104      </row>
 105
 106      <row>
 107       <entry><function>intset(int)</function></entry>
 108       <entry><type>int[]</type></entry>
 109       <entry>make single-element array</entry>
 110       <entry><literal>intset(42)</literal></entry>
 111       <entry><literal>{42}</literal></entry>
 112      </row>
 113
 114     </tbody>
 115    </tgroup>
 116   </table>
 117
 118   <table id="intarray-op-table">
 119    <title><filename>intarray</> Operators</title>
 120
 121    <tgroup cols="3">
 122     <thead>
 123      <row>
 124       <entry>Operator</entry>
 125       <entry>Returns</entry>
 126       <entry>Description</entry>
 127      </row>
 128     </thead>
 129
 130     <tbody>
 131      <row>
 132       <entry><literal>int[] &amp;&amp; int[]</literal></entry>
 133       <entry><type>boolean</type></entry>
 134       <entry>overlap &mdash; <literal>true</> if arrays have at least one common element</entry>
 135      </row>
 136      <row>
 137       <entry><literal>int[] @&gt; int[]</literal></entry>
 138       <entry><type>boolean</type></entry>
 139       <entry>contains &mdash; <literal>true</> if left array contains right array</entry>
 140      </row>
 141      <row>
 142       <entry><literal>int[] &lt;@ int[]</literal></entry>
 143       <entry><type>boolean</type></entry>
 144       <entry>contained &mdash; <literal>true</> if left array is contained in right array</entry>
 145      </row>
 146      <row>
 147       <entry><literal># int[]</literal></entry>
 148       <entry><type>int</type></entry>
 149       <entry>number of elements in array</entry>
 150      </row>
 151      <row>
 152       <entry><literal>int[] # int</literal></entry>
 153       <entry><type>int</type></entry>
 154       <entry>index (same as <function>idx</> function)</entry>
 155      </row>
 156      <row>
 157       <entry><literal>int[] + int</literal></entry>
 158       <entry><type>int[]</type></entry>
 159       <entry>push element onto array (add it to end of array)</entry>
 160      </row>
 161      <row>
 162       <entry><literal>int[] + int[]  </literal></entry>
 163       <entry><type>int[]</type></entry>
 164       <entry>array concatenation (right array added to the end of left one)</entry>
 165      </row>
 166      <row>
 167       <entry><literal>int[] - int</literal></entry>
 168       <entry><type>int[]</type></entry>
 169       <entry>remove entries matching right argument from array</entry>
 170      </row>
 171      <row>
 172       <entry><literal>int[] - int[]</literal></entry>
 173       <entry><type>int[]</type></entry>
 174       <entry>remove elements of right array from left</entry>
 175      </row>
 176      <row>
 177       <entry><literal>int[] | int</literal></entry>
 178       <entry><type>int[]</type></entry>
 179       <entry>union of arguments</entry>
 180      </row>
 181      <row>
 182       <entry><literal>int[] | int[]</literal></entry>
 183       <entry><type>int[]</type></entry>
 184       <entry>union of arrays</entry>
 185      </row>
 186      <row>
 187       <entry><literal>int[] &amp; int[]</literal></entry>
 188       <entry><type>int[]</type></entry>
 189       <entry>intersection of arrays</entry>
 190      </row>
 191      <row>
 192       <entry><literal>int[] @@ query_int</literal></entry>
 193       <entry><type>boolean</type></entry>
 194       <entry><literal>true</> if array satisfies query (see below)</entry>
 195      </row>
 196      <row>
 197       <entry><literal>query_int ~~ int[]</literal></entry>
 198       <entry><type>boolean</type></entry>
 199       <entry><literal>true</> if array satisfies query (commutator of <literal>@@</>)</entry>
 200      </row>
 201     </tbody>
 202    </tgroup>
 203   </table>
 204
 205   <para>
 206    (Before PostgreSQL 8.2, the containment operators <literal>@&gt;</> and
 207    <literal>&lt;@</> were respectively called <literal>@</> and <literal>~</>.
 208    These names are still available, but are deprecated and will eventually be
 209    retired.  Notice that the old names are reversed from the convention
 210    formerly followed by the core geometric datatypes!)
 211   </para>
 212
 213   <para>
 214    The containment operators <literal>@&gt;</> and <literal>&lt;@</> are
 215    approximately equivalent to <productname>PostgreSQL</>'s built-in operators
 216    of the same names, except that they work only on integer arrays while the
 217    built-in operators work for any array type.  An important difference is
 218    that <filename>intarray</>'s operators do not consider an empty array to be
 219    contained in anything else.  This is consistent with the behavior of
 220    GIN-indexed queries, but not with the usual mathematical definition of
 221    containment.
 222   </para>
 223
 224   <para>
 225    The <literal>@@</> and <literal>~~</> operators test whether an array
 226    satisfies a <firstterm>query</>, which is expressed as a value of a
 227    specialized data type <type>query_int</>.  A <firstterm>query</>
 228    consists of integer values that are checked against the elements of
 229    the array, possibly combined using the operators <literal>&amp;</>
 230    (AND), <literal>|</> (OR), and <literal>!</> (NOT).  Parentheses
 231    can be used as needed.  For example,
 232    the query <literal>1&amp;(2|3)</> matches arrays that contain 1
 233    and also contain either 2 or 3.
 234   </para>
 235  </sect2>
 236
 237  <sect2>
 238   <title>Index Support</title>
 239
 240   <para>
 241    <filename>intarray</> provides index support for the
 242    <literal>&amp;&amp;</>, <literal>@&gt;</>, <literal>&lt;@</>,
 243    and <literal>@@</> operators, as well as regular array equality.
 244   </para>
 245
 246   <para>
 247    Two GiST index operator classes are provided:
 248    <literal>gist__int_ops</> (used by default) is suitable for
 249    small- to medium-size data sets, while
 250    <literal>gist__intbig_ops</> uses a larger signature and is more
 251    suitable for indexing large data sets (i.e., columns containing
 252    a large number of distinct array values).
 253    The implementation uses an RD-tree data structure with
 254    built-in lossy compression.
 255   </para>
 256
 257   <para>
 258    There is also a non-default GIN operator class
 259    <literal>gin__int_ops</> supporting the same operators.
 260   </para>
 261
 262   <para>
 263    The choice between GiST and GIN indexing depends on the relative
 264    performance characteristics of GiST and GIN, which are discussed elsewhere.
 265    As a rule of thumb, a GIN index is faster to search than a GiST index, but
 266    slower to build or update; so GIN is better suited for static data and GiST
 267    for often-updated data.
 268   </para>
 269  </sect2>
 270
 271  <sect2>
 272   <title>Example</title>
 273
 274   <programlisting>
 275 -- a message can be in one or more <quote>sections</>
 276 CREATE TABLE message (mid INT PRIMARY KEY, sections INT[], ...);
 277
 278 -- create specialized index
 279 CREATE INDEX message_rdtree_idx ON message USING GIST (sections gist__int_ops);
 280
 281 -- select messages in section 1 OR 2 - OVERLAP operator
 282 SELECT message.mid FROM message WHERE message.sections &amp;&amp; '{1,2}';
 283
 284 -- select messages in sections 1 AND 2 - CONTAINS operator
 285 SELECT message.mid FROM message WHERE message.sections @&gt; '{1,2}';
 286
 287 -- the same, using QUERY operator
 288 SELECT message.mid FROM message WHERE message.sections @@ '1&amp;2'::query_int;
 289   </programlisting>
 290  </sect2>
 291
 292  <sect2>
 293   <title>Benchmark</title>
 294
 295   <para>
 296    The source directory <filename>contrib/intarray/bench</> contains a
 297    benchmark test suite.  To run:
 298   </para>
 299
 300   <programlisting>
 301    cd .../bench
 302    createdb TEST
 303    psql TEST &lt; ../_int.sql
 304    ./create_test.pl | psql TEST
 305    ./bench.pl
 306   </programlisting>
 307
 308   <para>
 309    The <filename>bench.pl</> script has numerous options, which
 310    are displayed when it is run without any arguments.
 311   </para>
 312  </sect2>
 313
 314  <sect2>
 315   <title>Authors</title>
 316
 317   <para>
 318    All work was done by Teodor Sigaev (<email>teodor@sigaev.ru</email>) and
 319    Oleg Bartunov (<email>oleg@sai.msu.su</email>). See
 320    <ulink url="http://www.sai.msu.su/~megera/postgres/gist"></ulink> for
 321    additional information. Andrey Oktyabrski did a great work on adding new
 322    functions and operations.
 323   </para>
 324  </sect2>
 325
 326 </sect1>