F.20. intarray
Prev | Up | Appendix F. Additional Supplied Modules | Home | Next

## F.20. intarray

The `intarray` module provides a number of useful functions and operators for manipulating null-free arrays of integers. There is also support for indexed searches using some of the operators.

All of these operations will throw an error if a supplied array contains any NULL elements.

Many of these operations are only sensible for one-dimensional arrays. Although they will accept input arrays of more dimensions, the data is treated as though it were a linear array in storage order.

This module is considered “trusted”, that is, it can be installed by non-superusers who have `CREATE` privilege on the current database.

### F.20.1. `intarray` Functions and Operators

The functions provided by the `intarray` module are shown in Table F.9, the operators in Table F.10.

Table F.9. `intarray` Functions

Function Description Example(s)
`icount` ( `integer[]` ) → `integer` Returns the number of elements in the array. `icount('{1,2,3}'::integer[])` → `3`
`sort` ( `integer[]`, `dir` `text` ) → `integer[]` Sorts the array in either ascending or descending order. `dir` must be `asc` or `desc`. `sort('{1,3,2}'::integer[], 'desc')` → `{3,2,1}`
`sort` ( `integer[]` ) → `integer[]` `sort_asc` ( `integer[]` ) → `integer[]` Sorts in ascending order. `sort(array[11,77,44])` → `{11,44,77}`
`sort_desc` ( `integer[]` ) → `integer[]` Sorts in descending order. `sort_desc(array[11,77,44])` → `{77,44,11}`
`uniq` ( `integer[]` ) → `integer[]` Removes adjacent duplicates. Often used with `sort` to remove all duplicates. `uniq('{1,2,2,3,1,1}'::integer[])` → `{1,2,3,1}` `uniq(sort('{1,2,3,2,1}'::integer[]))` → `{1,2,3}`
`idx` ( `integer[]`, `item` `integer` ) → `integer` Returns index of the first array element matching `item`, or 0 if no match. `idx(array[11,22,33,22,11], 22)` → `2`
`subarray` ( `integer[]`, `start` `integer`, `len` `integer` ) → `integer[]` Extracts the portion of the array starting at position `start`, with `len` elements. `subarray('{1,2,3,2,1}'::integer[], 2, 3)` → `{2,3,2}`
`subarray` ( `integer[]`, `start` `integer` ) → `integer[]` Extracts the portion of the array starting at position `start`. `subarray('{1,2,3,2,1}'::integer[], 2)` → `{2,3,2,1}`
`intset` ( `integer` ) → `integer[]` Makes a single-element array. `intset(42)` → `{42}`

Table F.10. `intarray` Operators

Operator Description
`integer[]` `&&` `integer[]` → `boolean` Do arrays overlap (have at least one element in common)?
`integer[]` `@>` `integer[]` → `boolean` Does left array contain right array?
`integer[]` `<@` `integer[]` → `boolean` Is left array contained in right array?
`` `#` `integer[]` → `integer` Returns the number of elements in the array.
`integer[]` `#` `integer` → `integer` Returns index of the first array element matching the right argument, or 0 if no match. (Same as `idx` function.)
`integer[]` `+` `integer` → `integer[]` Adds element to end of array.
`integer[]` `+` `integer[]` → `integer[]` Concatenates the arrays.
`integer[]` `-` `integer` → `integer[]` Removes entries matching the right argument from the array.
`integer[]` `-` `integer[]` → `integer[]` Removes elements of the right array from the left array.
`integer[]` `|` `integer` → `integer[]` Computes the union of the arguments.
`integer[]` `|` `integer[]` → `integer[]` Computes the union of the arguments.
`integer[]` `&` `integer[]` → `integer[]` Computes the intersection of the arguments.
`integer[]` `@@` `query_int` → `boolean` Does array satisfy query? (see below)
`query_int` `~~` `integer[]` → `boolean` Does array satisfy query? (commutator of `@@`)

The operators `&&`, `@>` and `<@` are equivalent to PostgreSQL'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.

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

### F.20.2. Index Support

`intarray` provides index support for the `&&`, `@>`, and `@@` operators, as well as regular array equality.

Two parameterized GiST index operator classes are provided: `gist__int_ops` (used by default) is suitable for small- to medium-size data sets, while `gist__intbig_ops` uses a larger signature and is more suitable for indexing large data sets (i.e., columns containing a large number of distinct array values). The implementation uses an RD-tree data structure with built-in lossy compression.

`gist__int_ops` approximates an integer set as an array of integer ranges. Its optional integer parameter `numranges` determines the maximum number of ranges in one index key. The default value of `numranges` is 100. Valid values are between 1 and 253. Using larger arrays as GiST index keys leads to a more precise search (scanning a smaller fraction of the index and fewer heap pages), at the cost of a larger index.

`gist__intbig_ops` approximates an integer set as a bitmap signature. Its optional integer parameter `siglen` determines the signature length in bytes. The default signature length is 16 bytes. Valid values of signature length are between 1 and 2024 bytes. Longer signatures lead to a more precise search (scanning a smaller fraction of the index and fewer heap pages), at the cost of a larger index.

There is also a non-default GIN operator class `gin__int_ops`, which supports these operators as well as `<@`.

The choice between GiST and GIN indexing depends on the relative performance characteristics of GiST and GIN, which are discussed elsewhere.

### F.20.3. Example

    -- a message can be in one or more “sections”
    CREATE TABLE message (mid INT PRIMARY KEY, sections INT[], ...);

    -- create specialized index with signature length of 32 bytes
    CREATE INDEX message_rdtree_idx ON message USING GIST (sections gist__intbig_ops (siglen = 32));

    -- select messages in section 1 OR 2 - OVERLAP operator
    SELECT message.mid FROM message WHERE message.sections && '{1,2}';

    -- select messages in sections 1 AND 2 - CONTAINS operator
    SELECT message.mid FROM message WHERE message.sections @> '{1,2}';

    -- the same, using QUERY operator
    SELECT message.mid FROM message WHERE message.sections @@ '1&2'::query_int;

### F.20.4. Benchmark

The source directory `contrib/intarray/bench` contains a benchmark test suite, which can be run against an installed PostgreSQL server. (It also requires `DBD::Pg` to be installed.) To run:

    cd .../contrib/intarray/bench
    createdb TEST
    psql -c "CREATE EXTENSION intarray" TEST
    ./create_test.pl | psql TEST
    ./bench.pl

The `bench.pl` script has numerous options, which are displayed when it is run without any arguments.

### F.20.5. Authors

All work was done by Teodor Sigaev (`<teodor@sigaev.ru>`) and Oleg Bartunov (`<oleg@sai.msu.su>`). See http://www.sai.msu.su/~megera/postgres/gist/ for additional information. Andrey Oktyabrski did a great work on adding new functions and operations.

Prev | Up | Next
F.19. intagg | Home | F.21. isn