intarray

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

https://www.postgresql.org/docs/current/intarray.html

Overview

Package	Version	Category	License	Language
`intarray`	`1.5`	FUNC	PostgreSQL	C

ID	Extension	Bin	Lib	Load	Create	Trust	Reloc	Schema
4960	`intarray`	No	No	No	Yes	Yes	No	-

Related	`arraymath` `floatvec` `aggs_for_vecs` `aggs_for_arrays` `collection` `hstore` `pgbson` `parray_gin` `btree_gin` `btree_gist`

Version

PG18	PG17	PG16	PG15	PG14
1.5	1.5	1.5	1.5	1.5

Install

Note: This is a built-in contrib extension of PostgreSQL

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

intarray

Overview

Version

Install

Usage

Functions

Operators

Index Support

Was this page helpful?