CODEWITHSUNDEEP
arrayspostgresqlaggregate-functions
Alexandros
Alexandros

Reputation: 2200

Postgres GROUP BY integer arrays

My DB table in Postgres 9.3

| f1|f2 | f3
| 1 | 2 | {1,2,3}
| 1 | 3 | {4,5}

f1,f2 are integers f3 is integer[].

How can I get this:

SELECT f1, myfn(f3)
FROM dbTable
GROUP BY f1;

so that I get:

| 1 | {1,2,3,4,5}

Is there a similar function in Postgres like array_agg that concatenates arrays and creates a new array;

Upvotes: 9

Views: 3515

Answers (2)

IMSoP
IMSoP

Reputation: 97718

I don't think there's a built-in aggregate function for that, but you could easily create your own.

Looking at the array functions and operators available, the function you want is array_cat - the operator || is usually more convenient, but we need a function to create the aggregate, so would otherwise have to wrap it in one.

Since we already have a function to use, we can create a very simple aggregate based on it - start with an empty array, concatenate each new item in turn, and no special intermediate data or final steps are necessary.

The CREATE AGGREGATE statement would be as follows (note the use of the anyarray pseudo-type as both the input and output of the aggregate):

CREATE AGGREGATE array_cat_agg(anyarray) (
    SFUNC=array_cat,
    STYPE=anyarray,
    INITCOND='{}'
);

This should then allow you to write exactly what you wanted:

SELECT f1, array_cat_agg(f3)
FROM dbTable
GROUP BY f1;

Here's a demo of this in action on SQLFiddle.

Upvotes: 4

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125244

SQL Fiddle

First unnest then aggregate

select f1, array_agg(f3) as f3
from (
    select f1, unnest(f3) as f3
    from dbtable
) s
group by f1

To avoid duplicates and for a sorted output (intarry extension must be installed):

select f1, sort(uniq(array_agg(f3))) as f3
from (
    select f1, unnest(f3) as f3
    from dbtable
) s
group by f1

Upvotes: 7

Related Questions