Obtaining the same string_agg results in GPDB as easily as in Postgres

[Keaton Adams]

Is there an easy, efficient way in GPDB that I am overlooking to filter out the duplicates in the concatenated strings from the result set in this example?

SELECT a.farm_id, string_agg(DISTINCT a.name, ',') as animals, string_agg(DISTINCT t.name, ',') as tools

FROM animal a, tool t

WHERE a.farm_id = t.farm_id

GROUP BY a.farm_id;

PostgreSQL Output (which is the desired output):

FARM_ID    ANIMALS        TOOLS

1          cow,horse      axe,hammer,spade

Greenplum DB Output:

ERROR:  DISTINCT is supported only for single-argument aggregates

If I remove the distinct clauses, which is "close", the repeating values in the strings will cause problems in the downstream analysis work:

test=# SELECT a.farm_id, string_agg(a.name, ',') as animals, string_agg(t.name, ',') as tools

FROM  animal a, tool t 

WHERE a.farm_id = t.farm_id

GROUP BY a.farm_id;

 farm_id |            animals            |               tools

---------+-------------------------------+-----------------------------------

       1 | cow,horse,cow,horse,cow,horse | spade,spade,axe,axe,hammer,hammer

(1 row)

CREATE TABLE animal (

    id serial primary key,

    farm_id integer,

    name varchar

);

INSERT INTO animal (farm_id, name) VALUES (1, 'cow'), (1, 'horse');

CREATE TABLE tool (

    id serial primary key,

    farm_id integer,

    name varchar

);

INSERT INTO tool (farm_id, name) VALUES (1, 'hammer'), (1, 'spade'), (1, 'axe');

Thanks.

[Louis Mugnano]

One way to do it is to put it through array_agg first since that does support the distinct clause:

SELECT a.farm_id, array_to_string(array_agg(DISTINCT a.name),',') as animals, 

                                array_to_string(array_agg(DISTINCT t.name),',') as tools       

FROM animal a, tool t                                                                                                                                  

WHERE a.farm_id = t.farm_id                                                                                                                            

GROUP BY a.farm_id;

 farm_id |  animals  |      tools       

---------+-----------+------------------

       1 | cow,horse | axe,hammer,spade

(1 row)

--
You received this message because you are subscribed to the Google Groups "Greenplum Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to gpdb-users+unsubscribe@greenplum.org.
To post to this group, send email to gpdb-...@greenplum.org.
Visit this group at https://groups.google.com/a/greenplum.org/group/gpdb-users/.
For more options, visit https://groups.google.com/a/greenplum.org/d/optout.

[Keaton Adams]

Thanks.  This seems to work perfectly for what the ask is.

Appreciated.