How to Create Custom Aggregates in PostgreSQL

Abdul Wahab Junaid

3 years ago

How to Create Custom Aggregates in PostgreSQL

SUM(), AVG(), COUNT(), and ARRAY_AGG() cover a huge percentage of everyday aggregation needs, but sooner or later I run into a calculation that none of PostgreSQL’s built-in aggregates handle cleanly — a weighted average, a running product, a custom string concatenation with specific formatting, or a statistical calculation specific to my domain. Rather than pulling all the rows back to the application layer and aggregating there, PostgreSQL lets me define genuinely custom aggregate functions that run directly inside the database, take full advantage of the query planner, and can even be used in GROUP BY queries and window functions.

In this article, I’ll cover what custom aggregates are, how they work internally, the syntax for CREATE AGGREGATE, several practical examples, common troubleshooting scenarios, and best practices for building efficient, correct aggregate functions.

What Is a Custom Aggregate?

An aggregate function in PostgreSQL processes a set of input rows and reduces them to a single output value (or, for some cases, a small summary result). Internally, every aggregate — including the built-in ones — is defined using three core pieces:

A state transition function — called once per input row, taking the current internal state and the new row’s value, and returning an updated state.
An initial state value — the starting point before any rows have been processed.
An optional final function — transforms the internal state into the final output value, if the internal state representation differs from the desired output.

This is exactly the same “reduce” pattern found in most programming languages’ reduce/fold functions, just implemented at the SQL level with PostgreSQL managing how and when the transition function gets called across a result set.

Basic Syntax

CREATE AGGREGATE aggregate_name (input_type) (
    SFUNC = state_transition_function,
    STYPE = state_type,
    INITCOND = initial_condition,
    FINALFUNC = final_function
);

SFUNC — the transition function, which must accept the current state as its first argument and the new input value as its second, returning the updated state.
STYPE — the data type used to hold the internal running state.
INITCOND — the starting state value, provided as a text literal that gets cast to STYPE.
FINALFUNC — optional; if omitted, the final state value is returned directly as the aggregate’s result.

Example 1: A Custom “Product” Aggregate

PostgreSQL has SUM() but no built-in aggregate for multiplying values together. Let’s build one.

Step 1: Create the Transition Function

CREATE OR REPLACE FUNCTION multiply_state(current_state NUMERIC, next_value NUMERIC)
RETURNS NUMERIC
AS $$
BEGIN
    RETURN current_state * next_value;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Step 2: Create the Aggregate

CREATE AGGREGATE product_agg(NUMERIC) (
    SFUNC = multiply_state,
    STYPE = NUMERIC,
    INITCOND = '1'
);

Step 3: Use It

SELECT product_agg(value) FROM (VALUES (2), (3), (4)) AS t(value);
-- 24

I can use this exactly like any other aggregate, including with GROUP BY:

SELECT category, product_agg(multiplier) AS total_multiplier
FROM adjustments
GROUP BY category;

Example 2: A Weighted Average Aggregate

This is one of the most common “missing” aggregates I’ve had to build myself in real projects. A weighted average needs two inputs per row — a value and a weight — so I use a composite state type to track both the running weighted sum and the running total weight.

Step 1: Define the State Type

CREATE TYPE weighted_avg_state AS (
    weighted_sum NUMERIC,
    total_weight NUMERIC
);

Step 2: Create the Transition Function

CREATE OR REPLACE FUNCTION weighted_avg_transition(state weighted_avg_state, value NUMERIC, weight NUMERIC)
RETURNS weighted_avg_state
AS $$
BEGIN
    RETURN ROW(
        state.weighted_sum + (value * weight),
        state.total_weight + weight
    )::weighted_avg_state;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Step 3: Create the Final Function

CREATE OR REPLACE FUNCTION weighted_avg_final(state weighted_avg_state)
RETURNS NUMERIC
AS $$
BEGIN
    IF state.total_weight = 0 THEN
        RETURN NULL;
    END IF;
    RETURN state.weighted_sum / state.total_weight;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Step 4: Create the Aggregate

CREATE AGGREGATE weighted_average(NUMERIC, NUMERIC) (
    SFUNC = weighted_avg_transition,
    STYPE = weighted_avg_state,
    INITCOND = '(0,0)',
    FINALFUNC = weighted_avg_final
);

Step 5: Use It

SELECT weighted_average(score, weight)
FROM (VALUES (80, 2), (90, 3), (70, 1)) AS t(score, weight);

This is a genuinely practical aggregate — I’ve used almost exactly this pattern for computing weighted customer satisfaction scores across multiple survey questions with different importance weightings.

Example 3: Custom String Aggregation with Formatting

PostgreSQL has STRING_AGG(), but suppose I want a custom aggregate that concatenates values with a numbered prefix, like "1. Apple, 2. Banana, 3. Cherry".

CREATE TYPE numbered_list_state AS (
    result TEXT,
    counter INTEGER
);

CREATE OR REPLACE FUNCTION numbered_list_transition(state numbered_list_state, value TEXT)
RETURNS numbered_list_state
AS $$
DECLARE
    new_counter INTEGER := state.counter + 1;
    new_result TEXT;
BEGIN
    IF state.result = '' THEN
        new_result := new_counter || '. ' || value;
    ELSE
        new_result := state.result || ', ' || new_counter || '. ' || value;
    END IF;
    RETURN ROW(new_result, new_counter)::numbered_list_state;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION numbered_list_final(state numbered_list_state)
RETURNS TEXT
AS $$
BEGIN
    RETURN state.result;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE AGGREGATE numbered_list_agg(TEXT) (
    SFUNC = numbered_list_transition,
    STYPE = numbered_list_state,
    INITCOND = '("",0)',
    FINALFUNC = numbered_list_final
);

SELECT numbered_list_agg(fruit) FROM (VALUES ('Apple'), ('Banana'), ('Cherry')) AS t(fruit);
-- 1. Apple, 2. Banana, 3. Cherry

Using Custom Aggregates as Window Functions

One of the nicer properties of custom aggregates in PostgreSQL is that they automatically work as window functions too, without any extra definition:

SELECT
    category,
    value,
    product_agg(value) OVER (PARTITION BY category ORDER BY id) AS running_product
FROM adjustments;

This gives me a running product per category, computed the same way SUM() OVER (...) would give a running total — for free, just by defining the aggregate once.

Parallel-Safe Aggregates

For aggregates over very large tables, PostgreSQL can parallelize aggregation across multiple worker processes if I provide a COMBINEFUNC, which merges two partial states together:

CREATE OR REPLACE FUNCTION multiply_combine(state1 NUMERIC, state2 NUMERIC)
RETURNS NUMERIC
AS $$
BEGIN
    RETURN state1 * state2;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE AGGREGATE product_agg_parallel(NUMERIC) (
    SFUNC = multiply_state,
    STYPE = NUMERIC,
    INITCOND = '1',
    COMBINEFUNC = multiply_combine,
    PARALLEL = SAFE
);

Without a COMBINEFUNC, PostgreSQL cannot safely split the aggregation work across parallel workers, so for aggregates I expect to run over genuinely large datasets, I define one whenever the underlying operation is associative (which multiplication and weighted sums both are).

Common Use Cases

Weighted averages and custom statistical calculations not covered by built-in aggregates.
Custom string or JSON concatenation with specific formatting rules.
Running products, custom running totals, or domain-specific accumulation logic.
Bitwise or set-accumulation aggregates, such as combining a column of bit flags across rows into one combined flag set.
Specialized “first” or “last” value aggregates based on custom tie-breaking logic beyond what DISTINCT ON or FIRST_VALUE() window functions directly offer.

Troubleshooting Common Issues

“function does not exist” error when creating the aggregate This means the transition or final function’s signature doesn’t exactly match what CREATE AGGREGATE expects. I double-check that the transition function’s first parameter type matches STYPE exactly, and its return type also matches STYPE.

Aggregate returns NULL unexpectedly for an empty group By default, if there are no input rows for a group, aggregates typically return NULL (or whatever the final function produces from the initial state, if INITCOND is set). I explicitly test my aggregate against an empty result set to confirm it behaves the way I expect, rather than assuming.

Unexpected results with parallel query execution If I forgot to define COMBINEFUNC for an aggregate but the query planner still attempts parallel execution, PostgreSQL will simply avoid parallelizing that specific aggregate rather than producing wrong results — but performance won’t benefit from available workers. I check EXPLAIN output to confirm whether parallelization is actually happening as expected for large-table aggregations.

State type mutation causing subtle bugs If I use a mutable, reference-type internal state (like an array) and modify it in place inside the transition function instead of returning a new value, this can cause incorrect results in some circumstances, particularly with the internal STYPE internal used for very advanced aggregates. For STYPE values based on composite types or simple scalars (as in the examples above), this typically isn’t an issue, since PostgreSQL treats them as immutable values, but I keep this in mind for advanced aggregate development.

Performance degradation with a composite state type Aggregates using a composite STYPE, like the weighted average example, incur small overhead compared to scalar-state aggregates since PostgreSQL has to construct and deconstruct a row value for every input row. For extremely hot aggregation paths over huge tables, I benchmark against alternative implementations (like computing multiple built-in SUM()s side by side and combining them in the final SELECT) to see if a composite-state custom aggregate is actually necessary.

Best Practices

Mark all supporting functions IMMUTABLE when appropriate. This allows the planner more freedom in caching and reordering, and is a requirement for the aggregate to be usable in certain optimized contexts.
Always define INITCOND explicitly. Relying on an implicit NULL initial state can lead to confusing behavior on the very first row, particularly for composite state types.
Provide a COMBINEFUNC for associative operations on large tables. This is a small amount of extra work that pays off significantly for parallel query performance.
Test aggregates against edge cases explicitly — empty input sets, single-row input, NULL values mixed into the input, and very large row counts.
Keep the state type as simple as possible. Only reach for a composite STYPE when a single scalar genuinely can’t represent the running state; simpler state types perform better and are easier to reason about.
Document the aggregate’s exact semantics. A custom aggregate like weighted_average needs a comment explaining argument order (value, then weight, in this case) since it isn’t self-evident the way SUM() is.
Consider whether the built-ins can already do the job with a wrapper. Sometimes what feels like it needs a custom aggregate is actually just a combination of existing aggregates and expressions in the final SELECT — I check this first before investing in a full custom aggregate definition.

Example 4: A Custom “Mode” (Most Frequent Value) Aggregate

PostgreSQL doesn’t ship with a built-in statistical mode aggregate, which is a gap I’ve had to fill more than once. Here’s a version using a JSONB-backed frequency counter as the internal state:

CREATE OR REPLACE FUNCTION mode_transition(state JSONB, value TEXT)
RETURNS JSONB
AS $$
BEGIN
    IF value IS NULL THEN
        RETURN state;
    END IF;
    RETURN jsonb_set(
        state,
        ARRAY[value],
        to_jsonb(COALESCE((state->>value)::INTEGER, 0) + 1)
    );
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION mode_final(state JSONB)
RETURNS TEXT
AS $$
DECLARE
    key TEXT;
    best_key TEXT;
    best_count INTEGER := -1;
BEGIN
    FOR key IN SELECT jsonb_object_keys(state) LOOP
        IF (state->>key)::INTEGER > best_count THEN
            best_count := (state->>key)::INTEGER;
            best_key := key;
        END IF;
    END LOOP;
    RETURN best_key;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE AGGREGATE mode_agg(TEXT) (
    SFUNC = mode_transition,
    STYPE = JSONB,
    INITCOND = '{}',
    FINALFUNC = mode_final
);

SELECT mode_agg(category) FROM product_views;

This is a good illustration of how flexible the state/transition/final pattern really is — the internal state doesn’t have to be numeric at all; it can be any PostgreSQL type capable of representing the running accumulation I need, including JSONB, arrays, or composite types.

Modifying and Dropping Aggregates

Like operators, aggregates can’t be altered in place — I drop and recreate them when the definition needs to change:

DROP AGGREGATE IF EXISTS product_agg(NUMERIC);

CREATE AGGREGATE product_agg(NUMERIC) (
    SFUNC = multiply_state,
    STYPE = NUMERIC,
    INITCOND = '1'
);

If I only need to change the underlying transition or final function’s logic (not its signature), I can often just CREATE OR REPLACE those individual functions without touching the aggregate definition at all, since the aggregate simply references them by name and signature.

Inspecting Existing Aggregates

Before building a custom aggregate, I always check whether PostgreSQL already has what I need — the built-in list is larger than people often realize, including statistical aggregates like percentile_cont, stddev, and mode. Actually, PostgreSQL does have a built-in mode() ordered-set aggregate, and the hand-rolled example above is really meant to illustrate the pattern rather than to replace it in production — I’d use the built-in mode() WITHIN GROUP (ORDER BY category) in real code.

\da product_agg

SELECT aggfnoid::regprocedure, aggtransfn, aggfinalfn
FROM pg_aggregate
WHERE aggfnoid::text LIKE '%product_agg%';

Frequently Asked Questions

Can a custom aggregate accept more than two arguments? Yes — the weighted average example above already shows a two-argument aggregate (value and weight), and PostgreSQL supports multi-argument aggregates generally; the transition function simply needs to accept the state plus however many input arguments the aggregate is declared with.

Do custom aggregates support DISTINCT and ORDER BY inside the aggregate call? Standard DISTINCT support works automatically based on the input type’s equality operator. ORDER BY-sensitive aggregates (called “ordered-set aggregates”) require a more advanced definition using WITHIN GROUP syntax and a different creation syntax than the basic pattern covered in this guide.

What happens if my transition function is VOLATILE instead of IMMUTABLE? The aggregate will still work correctly, but PostgreSQL loses optimization opportunities, including parallel-safety, so I mark transition and final functions IMMUTABLE whenever their behavior genuinely only depends on their direct inputs.

Ordered-Set Aggregates: A Brief Mention

Beyond the basic transition-function pattern covered throughout this guide, PostgreSQL also supports a more advanced category called ordered-set aggregates, which are sensitive to the order of input values — the built-in percentile_cont and mode aggregates are examples. These are defined with a different, more involved syntax using CREATE AGGREGATE ... (ORDER BY ...) semantics and a hypothetical or direct argument distinction. I don’t cover the full syntax here since it’s a fairly specialized need, but it’s worth knowing this category exists if I ever find myself needing an aggregate whose result genuinely depends on input ordering rather than just accumulation, since forcing that kind of logic into the basic transition-function pattern described above generally won’t work correctly.

When a Custom Aggregate Isn’t Worth Building

Before investing time in a custom aggregate, I check whether a combination of existing built-ins in a single SELECT already gets me there. A weighted average, for instance, can sometimes be expressed as SUM(value * weight) / SUM(weight) directly in a query without any custom aggregate at all — the custom aggregate version I built earlier is most valuable specifically when I need that calculation reused across many queries, embedded inside a view, or used as a window function, where repeating the raw expression everywhere becomes error-prone and harder to maintain consistently.

Final Thoughts

Custom aggregates are a genuinely powerful, somewhat underused feature of PostgreSQL. Understanding the transition-function/state/final-function model demystifies not just how to build my own aggregates, but also how PostgreSQL’s own built-in aggregates work internally. Whenever I find myself pulling an entire result set into application code just to compute a specialized running calculation, building a proper custom aggregate almost always turns out to be both simpler and dramatically faster — since the computation happens right next to the data, takes advantage of PostgreSQL’s query planner, and even works automatically as a window function once it’s defined.