Intagg
Integer aggregator and enumerator functions
The intagg
extension provides integer aggregation and enumeration functions, which are useful for working with integer arrays and creating sequences. This extension is particularly helpful when you need to aggregate integer values or generate enumerated lists.
Your Nile database arrives with the intagg
extension already enabled.
Available Functions
The extension provides two main functions:
int_array_aggregate(integer)
- Aggregates integers into an arrayint_array_enum(integer[])
- Creates a set of integers from an array
Integer Array Aggregation
Here’s how to use the integer aggregation function:
Integer Array Enumeration
The int_array_enum
function unnesting aggregated arrays into rows. It is the opposite of int_array_aggregate
.
Performance Considerations
- The aggregation function is efficient for moderate-sized datasets
- For very large datasets, consider using native PostgreSQL array_agg() function
- The enumeration function is useful for small to medium-sized arrays
Limitations
- Works only with integer values
- Not suitable for very large arrays due to memory constraints
- The enumerated output maintains the order of the input array
Alternative Approaches
For some use cases, you might want to consider these PostgreSQL native alternatives:
array_agg()
for general-purpose array aggregationgenerate_series()
for generating sequencesunnest()
for array expansion
For more details, refer to the PostgreSQL documentation on aggregate functions.
The official documentation for the intagg
extension is here and includes a nice example of
using the intagg
functions to denormalize data and still be able to query it in a normalized way.