Thread: looking for alternative to MySQL's GROUP_CONCAT function
Hi all, I'm looking into PostgreSQL. Coming from a MySQL background, I have made heavy use of its very useful GROUP_CONCAT function. You can read about the function here: http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html Is there a PostgreSQL alternative to GROUP_CONCAT? Thanks, Ken
> Hi all, > > I'm looking into PostgreSQL. Coming from a MySQL background, > I have made heavy use of its very useful GROUP_CONCAT > function. You can read about the function > here: > > http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html > > Is there a PostgreSQL alternative to GROUP_CONCAT? Check the user comments at http://www.postgresql.org/docs/8.0/interactive/sql-createaggregate.html. Sounds like what you're looking for. //Magnus
<x3v0-pgsql@yahoo.com> writes: > I'm looking into PostgreSQL. Coming from a MySQL > background, I have made heavy use of its very useful > GROUP_CONCAT function. You can read about the function > here: > http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html > Is there a PostgreSQL alternative to GROUP_CONCAT? In Postgres it's customary to build this sort of thing out of spare parts. The basic spare part is a user-defined aggregate. You can make an aggregate over the built-in concatenation function: CREATE AGGREGATE concat ( BASETYPE = text, SFUNC = textcat, STYPE = text ); This does the basic task of concatenating the values found in a table group, but it doesn't supply any separator, so more likely you'd want to write a custom function that inserts a separator and then aggregate with that: create function textcatspace(text,text) returns text as $$ select $1 || ' ' || $2 $$ language sql strict immutable; CREATE AGGREGATE concat ( BASETYPE = text, SFUNC = textcatspace, STYPE = text ); (NB: the "strict" bit is essential to get this to match MySQL's semantics for NULLs in group_concat.) We don't unfortunately have any easy way to pass in different separator values --- you'd need to create a distinct function and aggregate for each separator string you want. Also, a function in plpgsql might be more efficient than one in SQL. The other part this isn't handling is controlling the order in which the inputs are concatenated. You can find that discussed in the archives: http://archives.postgresql.org/pgsql-general/2005-09/msg00034.php regards, tom lane
On Fri, Oct 21, 2005 at 11:58:07AM -0700, x3v0-pgsql@yahoo.com wrote: > Hi all, > > I'm looking into PostgreSQL. Coming from a MySQL background, I have > made heavy use of its very useful GROUP_CONCAT function. You can > read about the function here: > > http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html > > Is there a PostgreSQL alternative to GROUP_CONCAT? There's a more flexible one :) Use the array_accum() aggregate as described here http://www.postgresql.org/docs/current/static/xaggr.html and wrap it with array_to_string() http://www.postgresql.org/docs/current/static/functions-array.html to get a nice, flexible system. You can make a static one if you like. One thing about this approach is that you then have to exclude NULLs from your search because PostgreSQL arrays can't yet hold NULLs. Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!