1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
8
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
10
HREF="mailto:pgsql-docs@postgresql.org"><LINK
12
TITLE="PostgreSQL 9.1beta1 Documentation"
13
HREF="index.html"><LINK
15
TITLE="Additional Supplied Modules"
16
HREF="contrib.html"><LINK
19
HREF="intagg.html"><LINK
25
HREF="stylesheet.css"><META
26
HTTP-EQUIV="Content-Type"
27
CONTENT="text/html; charset=ISO-8859-1"><META
29
CONTENT="2011-04-27T21:20:33"></HEAD
35
SUMMARY="Header navigation table"
47
>PostgreSQL 9.1beta1 Documentation</A
66
TITLE="Additional Supplied Modules"
74
>Appendix F. Additional Supplied Modules</TD
80
TITLE="Additional Supplied Modules"
111
> module provides a number of useful functions
112
and operators for manipulating null-free arrays of integers.
113
There is also support for indexed searches using some of the operators.
116
> All of these operations will throw an error if a supplied array contains any
120
> Many of these operations are only sensible for one-dimensional arrays.
121
Although they will accept input arrays of more dimensions, the data is
122
treated as though it were a linear array in storage order.
133
> Functions and Operators</A
138
NAME="INTARRAY-FUNC-TABLE"
150
><COL><COL><COL><COL><COL><THEAD
177
>number of elements in array</TD
181
>icount('{1,2,3}'::int[])</TT
193
>sort(int[], text dir)</CODE
201
>sort array — <TT
214
>sort('{1,2,3}'::int[], 'desc')</TT
234
>sort in ascending order</TD
238
>sort(array[11,77,44])</TT
250
>sort_asc(int[])</CODE
258
>sort in ascending order</TD
274
>sort_desc(int[])</CODE
282
>sort in descending order</TD
306
>remove adjacent duplicates</TD
310
>uniq(sort('{1,2,3,2,1}'::int[]))</TT
322
>idx(int[], int item)</CODE
330
>index of first element matching <TT
337
>idx(array[11,22,33,22,11], 22)</TT
349
>subarray(int[], int start, int len)</CODE
357
>portion of array starting at position <TT
367
>subarray('{1,2,3,2,1}'::int[], 2, 3)</TT
379
>subarray(int[], int start)</CODE
387
>portion of array starting at position <TT
394
>subarray('{1,2,3,2,1}'::int[], 2)</TT
414
>make single-element array</TD
432
NAME="INTARRAY-OP-TABLE"
444
><COL><COL><COL><THEAD
459
>int[] && int[]</TT
470
> if arrays have at least one common element</TD
476
>int[] @> int[]</TT
484
>contains — <TT
487
> if left array contains right array</TD
493
>int[] <@ int[]</TT
501
>contained — <TT
504
> if left array is contained in right array</TD
518
>number of elements in array</TD
532
>index (same as <CODE
549
>push element onto array (add it to end of array)</TD
563
>array concatenation (right array added to the end of left one)</TD
577
>remove entries matching right argument from array</TD
591
>remove elements of right array from left</TD
605
>union of arguments</TD
625
>int[] & int[]</TT
633
>intersection of arrays</TD
639
>int[] @@ query_int</TT
650
> if array satisfies query (see below)</TD
656
>query_int ~~ int[]</TT
667
> if array satisfies query (commutator of <TT
676
> (Before PostgreSQL 8.2, the containment operators <TT
683
> were respectively called <TT
690
These names are still available, but are deprecated and will eventually be
691
retired. Notice that the old names are reversed from the convention
692
formerly followed by the core geometric data types!)
705
> are equivalent to <SPAN
709
operators of the same names, except that they work only on integer arrays
710
that do not contain nulls, while the built-in operators work for any array
711
type. This restriction makes them faster than the built-in operators
721
> operators test whether an array
725
>, which is expressed as a value of a
726
specialized data type <TT
733
consists of integer values that are checked against the elements of
734
the array, possibly combined using the operators <TT
745
can be used as needed. For example,
749
> matches arrays that contain 1
750
and also contain either 2 or 3.
759
>F.18.2. Index Support</A
765
> provides index support for the
779
> operators, as well as regular array equality.
782
> Two GiST index operator classes are provided:
786
> (used by default) is suitable for
787
small- to medium-size data sets, while
790
>gist__intbig_ops</TT
791
> uses a larger signature and is more
792
suitable for indexing large data sets (i.e., columns containing
793
a large number of distinct array values).
794
The implementation uses an RD-tree data structure with
795
built-in lossy compression.
798
> There is also a non-default GIN operator class
802
> supporting the same operators.
805
> The choice between GiST and GIN indexing depends on the relative
806
performance characteristics of GiST and GIN, which are discussed elsewhere.
807
As a rule of thumb, a GIN index is faster to search than a GiST index, but
808
slower to build or update; so GIN is better suited for static data and GiST
809
for often-updated data.
821
CLASS="PROGRAMLISTING"
822
>-- a message can be in one or more <SPAN
826
CREATE TABLE message (mid INT PRIMARY KEY, sections INT[], ...);
828
-- create specialized index
829
CREATE INDEX message_rdtree_idx ON message USING GIST (sections gist__int_ops);
831
-- select messages in section 1 OR 2 - OVERLAP operator
832
SELECT message.mid FROM message WHERE message.sections && '{1,2}';
834
-- select messages in sections 1 AND 2 - CONTAINS operator
835
SELECT message.mid FROM message WHERE message.sections @> '{1,2}';
837
-- the same, using QUERY operator
838
SELECT message.mid FROM message WHERE message.sections @@ '1&2'::query_int;</PRE
846
>F.18.4. Benchmark</A
849
> The source directory <TT
851
>contrib/intarray/bench</TT
853
benchmark test suite. To run:
856
CLASS="PROGRAMLISTING"
859
psql TEST < ../_int.sql
860
./create_test.pl | psql TEST
866
> script has numerous options, which
867
are displayed when it is run without any arguments.
879
> All work was done by Teodor Sigaev (<CODE
882
HREF="mailto:teodor@sigaev.ru"
889
HREF="mailto:oleg@sai.msu.su"
894
HREF="http://www.sai.msu.su/~megera/postgres/gist/"
896
>http://www.sai.msu.su/~megera/postgres/gist/</A
898
additional information. Andrey Oktyabrski did a great work on adding new
899
functions and operations.
908
SUMMARY="Footer navigation table"
b'\\ No newline at end of file'