Intarray
Additional functions and operators for integer arrays
The intarray
extension provides additional functions and operators for working with arrays of integers. It’s particularly useful when you need to perform operations like unions, intersections, or searches on integer arrays without writing complex SQL queries.
Your Nile database arrives with the intarray
extension already enabled.
Operators
The extension provides several operators for array manipulation:
&&
- overlap (have elements in common)@>
- contains<@
- is contained by=
- equal+
- union&
- intersection-
- difference
Basic Array Operations
Here’s how to use the basic array operations:
Array Set Operations
The extension provides set operations for combining arrays:
Array Manipulation Functions
The extension includes several useful functions:
Query Optimization
The extension supports GiST and GIN indexes for efficient array operations:
These indexes can significantly improve performance for the following types of queries:
- Overlap (
&&
) - Contains (
@>
) - Contained by (
<@
) - Equal (
=
)
Performance Considerations
- GIN indexes are typically better for exact matches and contained-by queries
- GiST indexes are better for overlap queries but may be less precise
- Array operations are performed in memory, so be cautious with very large arrays
- Sorting and uniqueness operations (
sort
,uniq
) create new arrays
Limitations
- Works only with integer arrays
- No support for multi-dimensional arrays
- Array size is limited by available memory
- Some operations create copies of arrays, which can impact memory usage
Alternative Approaches
For some use cases, you might want to consider:
- Using junction tables for many-to-many relationships
- Using native PostgreSQL array functions
- Using JSONB arrays for more flexible data types
For more details, refer to the PostgreSQL documentation on arrays and the intarray extension documentation.