February 17th, 2010 by depesz | Tags: , , , , | 5 comments »
Did it help? If yes - maybe you can help me? Donate BTC to 19zPa5diT2LZqGtTi8f8bfApLn8rw9zBHx

On 1st of Februyary, Takahiro Itagaki committed a patch by Pavel Stehule which adds string_agg aggregate:

Log Message:
-----------
Add string_agg aggregate functions. The one argument version concatenates
the input values into a string. The two argument version also does the same
thing, but inserts delimiters between elements.
 
Original patch by Pavel Stehule, reviewed by David E. Wheeler and me.

Generally it's usage is pretty simple:

# select string_agg(x) from ( values ('d'), ('e'), ('p'), ('e'), ('s'), ('z') ) as t (x);
string_agg
------------
depesz
(1 row)

or

# select string_agg( relname, ', ' ) from (select relname from pg_class where relkind = 'r' limit 5 )x;
string_agg
--------------------------------------------------------------------
pg_statistic, pg_type, pg_attribute, x, active_threads_by_forum_id
(1 row)

This functionality isn't actually really new – I wrote (sorry, no English version available, only Polish) about something like this back in 2006. Later on we got array_agg – general aggregate that makes arrays – which can be used with array_to_string function to get the same output.

What's interesting is its speed.

David Wheeler benchmarked it against array_to_string(array_agg) (kind of), and got following results on some dataset:

  • array_to_string(array_agg) – 1365.382 ms
  • string_agg – 17.989 ms

That's sweet.

  1. 5 comments

  2. Why would you use this in the wild?

  3. Feb 17, 2010

    Any kinds or reports.

  4. # kspark
    Feb 18, 2010

    so sweet. I am using string agg in wilds a lot, because of such needs-human tends to see data in compact way, not relational way

  5. # Corey
    Feb 18, 2010

    I second what kspark said.
    Also, programs often need a unique serial key to identify a set of rows. string_agg() on their sorted primary keys is one way to do that.

  6. Sep 28, 2011

    string_agg() is just what I need for a report today. I’m supposed to produce a comma-separated list of emails in a single column, which would normally be returned as multiple rows. I looked at several other alternatives, like pivot tables (in the database with crosstab() or a spreadsheet, or adding a processing step in Perl, but string_agg() will be the simplest solution.

Leave a comment