CODEWITHSUNDEEP
sqlpostgresqlstring-aggregation
yen
yen

Reputation: 2342

Concatenate all the string values returned by distinct()

UPDATE / NOTE ON ANSWERS BELOW pg_typeof() on my target column was "regtype", not "text"...

So the contrived example below was a red herring. it WAS answered correctly by @richyen below. For those whose tables are NOT text, converting to text is needed, which is the accepted answer by @a_horse_with_no_name below

From table myschema.thing:

id | fruit  | color  | owner_id
---+--------+--------+---------
1  | apple  | red    |   100
2  | banana | yellow |   100
3  | tomato | red    |   500
4  | grape  | purple |   200

I'm trying to get the result: 

colors
------------------
red,yellow,purple

Based on this page: https://www.postgresql.org/docs/11/functions-string.html

I've tried this:

select concat_ws(',', distinct(color)) as colors 
from myschema.thing

But it doesn't work. Where am I going wrong? Thanks in advance.

Upvotes: 2

Views: 967

Answers (2)

user330315
user330315

Reputation:

User string_agg()

select string_agg(distinct color::text, ',') as colors
from thing

Online example: https://rextester.com/GLFXG32756

Upvotes: 0

richyen
richyen

Reputation: 10048

I think you should use string_agg(), with a distinct clause:

postgres=# create table thing (id int, color text);
CREATE TABLE
postgres=# insert into thing values (1, 'red'),(2,'yellow'),(3,'red'),(4,'purple');
INSERT 0 4
postgres=# select * from thing;
 id |  color  
----+--------
  1 | red
  2 | yellow
  3 | red
  4 | purple
(4 rows)

postgres=# select string_agg(distinct color, ',') as colors from myschema.thing;
      colors       
-------------------
 purple,red,yellow
(1 row)

Upvotes: 2

Related Questions