PostgreSQL 9.1: How to concatenate rows in array without duplicates, JOIN another table

Question

I am using PostgreSQL 9.1 and need help with concatenating multiple rows in one. I need to do that in 2 tables. When I use two times array_agg() functions I get duplicated values in result.

Tables:

CREATE TABLE rnp (id int, grp_id int, cabinets varchar(15) );  INSERT INTO rnp VALUES  (1,'11','cabs1') ,(2,'11','cabs2') ,(3,'11','cabs3') ,(4,'11','cabs4') ,(5,'22','c1') ,(6,'22','c2');  CREATE TABLE ips (id int, grp_id int, address varchar(15));  INSERT INTO ips VALUES  (1,'11','NY') ,(2,'11','CA') ,(3,'22','DC') ,(4,'22','LA'); 

SQL:

SELECT DISTINCT    rnp.grp_id,   array_to_string(array_agg(rnp.cabinets)OVER (PARTITION BY rnp.grp_id), ',') AS cabinets,   array_to_string(array_agg(ips.address) OVER (PARTITION BY ips.grp_id), ',') AS addresses   FROM rnp JOIN ips ON rnp.grp_id=ips.grp_id 

Result:

GRP_ID  CABINETS                                             ADDRESSES 11  cabs1,cabs1,cabs2,cabs2,cabs3,cabs3,cabs4,cabs4     NY,CA,NY,CA,NY,CA,NY,CA 22  c1,c1,c2,c2                                             DC,LA,DC,LA 

And what I need is:

 GRP_ID     CABINETS                 ADDRESSES     11  cabs1,cabs2,cabs3,cabs4       NY,CA,     22  c1,c2                         DC,LA 

This example in SQLFiddle: http://sqlfiddle.com/#!1/4815e/19

There is no problem if use one table - SQLFiddle: http://sqlfiddle.com/#!1/4815e/20

What am I missing? Is it possible to do this, because of JOIN?

Answer 1

Instead of using window functions and patitioning, use a query-level GROUP BY and aggregate with a DISTINCT clause:

SELECT            rnp.grp_id,   array_to_string(array_agg(distinct rnp.cabinets),',') AS cabinets,   array_to_string(array_agg(distinct ips.address),',')  AS addresses FROM rnp JOIN ips ON rnp.grp_id=ips.grp_id GROUP BY rnp.grp_id, ips.grp_id; 

Result:

 grp_id |        cabinets         | addresses  --------+-------------------------+-----------      11 | cabs1,cabs2,cabs3,cabs4 | CA,NY      22 | c1,c2                   | DC,LA (2 rows) 

The key here is that instead of using window functions and patitioning, you use a query-level GROUP BY and aggregate with a DISTINCT clause.

This'd work with the window function approach too, except that PostgreSQL (9.1 at least) doesn't support DISTINCT in window functions:

regress=# SELECT DISTINCT   rnp.grp_id,   array_to_string(array_agg(distinct rnp.cabinets)OVER (PARTITION BY rnp.grp_id), ',') AS cabinets,                       array_to_string(array_agg(distinct ips.address) OVER (PARTITION BY ips.grp_id), ',') AS addresses FROM rnp JOIN ips ON rnp.grp_id=ips.grp_id; ERROR:  DISTINCT is not implemented for window functions LINE 3:   array_to_string(array_agg(distinct rnp.cabinets)OVER (PART...