intarray

mdx: format: md

The intarray module provides a number of useful functions and operators for manipulating arrays of integers without null values. It also provides index search support for certain operators.

If any of the supplied arrays contain NULL elements, all operations will throw an error. Many of these operations only make sense for one-dimensional arrays. Although they will accept arrays with more dimensions as input, the data will be treated as a linear array arranged in storage order.

This module is considered "trusted", meaning it can be installed by non-superusers who have CREATE privilege on the current database.

1. intarray Functions and Operators

The functions provided by the intarray module are listed in Table C.9, and the operators are listed in Table C.10.

Table C.9. intarray Functions

Function/Description/Example
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 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 uniq(sort('{1,2,3,2,1}'::integer[])) → {1,2,3}
idx ( integer[], item integer ) → integer Returns the index of the first element matching item (0 if none) 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 consisting of 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[] Creates a single-element array intset(42) → {42}

Table C.10. intarray Operators

Operator/Description
integer[] && integer[] → boolean Overlap — whether the arrays have at least one element in common
integer[] @> integer[] → boolean Whether the left array contains the right array
integer[] @< integer[] → boolean Whether the left array is contained by the right array
## integer[] → integer Returns the number of elements in the array
integer[] ## integer → integer Returns the index of the first array element matching the right argument, or 0 if no match. (Same as the idx function.)
integer[] + integer → integer[] Appends the element to the end of the array.
integer[] + integer[] → integer[] Concatenates the arrays.
integer[] - integer → integer[] Removes items 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 arrays
integer[] & integer[] → integer[] Computes the intersection of the arguments
integer[] @@ query_int → boolean Whether the array satisfies the query
query_int ~~ integer[] → boolean Whether the array satisfies the query

The operators &&, @>, and <@ are equivalent to PostgreSQL's built-in operators of the same names, but they work only on arrays of integers without null values, while the built-in operators work on 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 represented as a value of the special data type query_int. A query consisting of integer values is 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.

C.18.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-sized data sets, while gist__intbig_ops uses a larger signature and is better suited for indexing large data sets (i.e., columns containing a large number of distinguishable array values). The implementation uses an RD-tree structure with built-in lossy compression.

gist__int_ops approximates integer sets as arrays of integer ranges. Its optional integer parameter numrange determines the maximum number of ranges in an 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 more precise searches (scanning a smaller portion of the index and fewer heap pages), at the cost of a larger index.

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

There is also a non-default GIN operator class gin__int_ops that supports the same operators.

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

3. Examples

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

-- Create a dedicated index with a signature length of 32 bytes CREATE INDEX message_rdtree_idx ON message USING GIST (sections gist__int_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}';

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