intarray

functions, operators, and index support for 1-D arrays of integers

intarray : functions, operators, and index support for 1-D arrays of integers

Overview

ID	Extension	Package	Version	Category	License	Language
4960	intarray	intarray	`1.5`	FUNC	PostgreSQL	C

Attribute	Has Binary	Has Library	Need Load	Has DDL	Relocatable	Trusted
----dt-	No	No	No	Yes	no	yes

Relationships
See Also	arraymath floatvec aggs_for_vecs aggs_for_arrays collection hstore pgbson parray_gin btree_gin btree_gist

Packages

PG18	PG17	PG16	PG15	PG14
1.5	1.5	1.5	1.5	1.5

This is a built-in contrib extension ship with the PostgreSQL kernel

Install

Create this extension with:

CREATE EXTENSION intarray;

Usage

intarray: integer array functions and operators with index support

Provides functions and operators for manipulating null-free integer arrays, with GiST and GIN index support for fast array searches.

CREATE EXTENSION intarray;

Functions

Function	Description	Example
`icount(int[])`	Number of elements	`icount('{1,2,3}')` – 3
`sort(int[], dir)`	Sort array (`'asc'` or `'desc'`)	`sort('{3,1,2}','asc')` – `{1,2,3}`
`sort_asc(int[])`	Sort ascending	`sort_asc('{3,1,2}')` – `{1,2,3}`
`sort_desc(int[])`	Sort descending	`sort_desc('{3,1,2}')` – `{3,2,1}`
`uniq(int[])`	Remove adjacent duplicates	`uniq(sort('{1,2,3,2,1}'))` – `{1,2,3}`
`idx(int[], item)`	Index of first match	`idx('{11,22,33}', 22)` – 2
`subarray(int[], start, len)`	Extract sub-array	`subarray('{1,2,3,4}', 2, 2)` – `{2,3}`
`intset(int)`	Make single-element array	`intset(42)` – `{42}`

Operators

Operator	Description
`&&`	Arrays overlap (have common elements)
`@>`	Left array contains right
`<@`	Left array is contained in right
`#`	Number of elements
`+`	Array concatenation / append element
`-`	Remove elements
`\|`	Union of arrays
`&`	Intersection of arrays
`@@`	Array matches a query expression
`~~`	Query expression matches array

Index Support

-- GiST index for array containment/overlap queries
CREATE INDEX idx ON messages USING GIST (tags gist__intbig_ops);

-- GIN index (alternative)
CREATE INDEX idx ON messages USING GIN (tags gin__int_ops);

-- Query with index support
SELECT * FROM messages WHERE tags && '{1,2}';     -- overlap
SELECT * FROM messages WHERE tags @> '{1,2}';     -- contains
SELECT * FROM messages WHERE tags @@ '1&(2|3)';  -- query expression