Friday, April 20, 2007

Porting mysql group_concat to postgres

There's a very useful aggregate function in mysql that allows you to concatenate several row values into one single row value. Here is an example:



mysql> create table t1 (
-> t1_id numeric,
-> col2 varchar(50)
-> );
Query OK, 0 rows affected (0.18 sec)

mysql> create table t2 (
-> t2_id numeric,
-> t1_id numeric,
-> col2 varchar(50)
-> );
Query OK, 0 rows affected (0.17 sec)

mysql> insert into t1 values (1,'a'),(2,'b'),(3,'c');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> insert into t2 values (1,1,'d'),(2,1,'f'),(3,2,'g'),(4,3,'f'),(5,3,'h'),(6,3,'i'),(7,3,'j');
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0

mysql> select * from t1;
+-------+------+
| t1_id | col2 |
+-------+------+
| 1 | a |
| 2 | b |
| 3 | c |
+-------+------+
3 rows in set (0.01 sec)

mysql> select * from t2;
+-------+-------+------+
| t2_id | t1_id | col2 |
+-------+-------+------+
| 1 | 1 | d |
| 2 | 1 | f |
| 3 | 2 | g |
| 4 | 3 | f |
| 5 | 3 | h |
| 6 | 3 | i |
| 7 | 3 | j |
+-------+-------+------+
7 rows in set (0.00 sec)

mysql> select *,(select group_concat(col2) from t2 where t1_id = t.t1_id) from t1 as t;
+-------+------+-----------------------------------------------------------+
| t1_id | col2 | (select group_concat(col2) from t2 where t1_id = t.t1_id) |
+-------+------+-----------------------------------------------------------+
| 1 | a | d,f |
| 2 | b | g |
| 3 | c | f,h,i,j |
+-------+------+-----------------------------------------------------------+
3 rows in set (0.17 sec)


Postgres as you may have noticed doesn't have a function like this, so I created my own approach:

Remember that you should have enabled pspgsql for your database previously:

In the shell you must type :

createlang plpgsql -Upostgres -d template1



template1=# CREATE FUNCTION concat (text, text) RETURNS text AS '
template1'# DECLARE
template1'# result text;
template1'# BEGIN
template1'# IF $1 is not null THEN
template1'# result := $1 || $2;
template1'# END IF;
template1'#
template1'# RETURN result;
template1'# END;
template1'# ' LANGUAGE plpgsql;
CREATE FUNCTION

template1=# CREATE AGGREGATE row_concat(
template1(# sfunc = concat,
template1(# basetype = text,
template1(# stype = text,
template1(# initcond = ''
template1(# );
CREATE AGGREGATE


template1=# create table t1 (
template1(# t1_id numeric,
template1(# col2 varchar(50)
template1(# );
CREATE TABLE
template1=#
template1=# create table t2 (
template1(# t2_id numeric,
template1(# t1_id numeric,
template1(# col2 varchar(50)
template1(# );
CREATE TABLE

template1=# insert into t1 values (1,'a');
INSERT 0 1
template1=# insert into t1 values (2,'b');
INSERT 0 1
template1=# insert into t1 values (3,'c');
INSERT 0 1

INSERT 0 1
template1=# insert into t2 values (1,1,'d');
INSERT 0 1
template1=# insert into t2 values (2,1,'f');
INSERT 0 1
template1=# insert into t2 values (3,2,'g');
INSERT 0 1
template1=# insert into t2 values (4,3,'f');
INSERT 0 1
template1=# insert into t2 values (5,3,'h');
INSERT 0 1
template1=# insert into t2 values (6,3,'i');
INSERT 0 1
template1=# insert into t2 values (7,3,'j');
INSERT 0 1

template1=# select *,(select row_concat(col2) from t2 where t1_id = t.t1_id) from t1 as t;
t1_id | col2 | ?column?
-------+------+----------
1 | a | df
2 | b | g
3 | c | fhij
(3 rows)




I didn't want to set a comma as the default separator so the user can customize easily the string. Another important thing is that if there is a null value in any row then the result will be null, like most of the aggregate functions.


template1=# select *,(select row_concat(coalesce(col2,'') || ' , ') from t2 where t1_id = t.t1_id) from t1 as t;
t1_id | col2 | ?column?
-------+------+------------------
1 | a | d , f ,
2 | b | g ,
3 | c | f , h , i , j ,
(3 rows)

5 comments:

Anonymous said...

I'm trying to do this very thing, I have mysql 5.0. Do i need to create a stored procedure for this? If you can be any help, I would apprecaite it lots.

Carlos said...

Nop, you don't have to create a stored procedure, group_concat comes built-in in mysql 5.0. Are you getting any error?

benik9 said...

Postgresql has a nicer way in fact.

select *, array_to_string(array(select col2 from t2 where t1_id=t1.t1_id),',') from t1;

Carlos said...

You're right . When I was looking for a solution I didn't check array_to_string, it seems to be not very documented. Well still this solution works for versions prior to Postgresql 7.4. since that's the release when it was added.

Nandkishor Wagh said...

attractive piece of information, I had come to know about your blog from my friend arjun, ahmedabad,i have read atleast eleven posts of yours by now, and let me tell you, your website gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a lot once again, Regards, Single Row Function in sql