Waiting for 9.0 – string_agg

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.

5 thoughts on “Waiting for 9.0 – string_agg”

  1. 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

  2. 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.

  3. 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.

Comments are closed.