Skip to content

Instantly share code, notes, and snippets.

@ryansmith3136

ryansmith3136/agg.sql

Created January 23, 2013 05:19
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save ryansmith3136/4602274 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save ryansmith3136/4602274 to your computer and use it in GitHub Desktop.
Download ZIP
Postgres array concatenation aggregate function.
Raw
agg.sql
CREATE AGGREGATE array_accum (anyarray)
(
sfunc = array_cat,
stype = anyarray,
initcond = '{}'
);
@tkalfigo

tkalfigo commented Sep 29, 2017

Copy link
Copy Markdown

Useful and works like a charm. Perhaps consider proposing on the pg-dev list to include it in future version.

@richddr

richddr commented Nov 20, 2019

Copy link
Copy Markdown

This is pretty sweet 👍

@andcri

andcri commented Feb 24, 2020

Copy link
Copy Markdown

Great!

@Medosopher

Medosopher commented Aug 29, 2020

Copy link
Copy Markdown

Great Job. Thanks.

@jbdesbas

jbdesbas commented Feb 1, 2021

Copy link
Copy Markdown

Simple and useful ! Thanks !

@bf

bf commented Jul 13, 2022

Copy link
Copy Markdown

had to change it for pg 14:

CREATE AGGREGATE array_accum (anycompatiblearray)
(
    sfunc = array_cat,
    stype = anycompatiblearray,
    initcond = '{}'
);

@WhereRtheInterwebs

WhereRtheInterwebs commented Apr 22, 2023 •
edited
Loading

Copy link
Copy Markdown

This is great! Is it possible to adapt this so that you can make the array items distinct?
Example of what I'm thinking:
array_accum(distinct "duplicateList")

@megagreg72

megagreg72 commented Aug 1, 2024 •
edited
Loading

Copy link
Copy Markdown

had to change it for pg 14:

CREATE AGGREGATE array_accum (anycompatiblearray)
(
    sfunc = array_cat,
    stype = anycompatiblearray,
    initcond = '{}'
);

Thanks, this version worked for me!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment