working with strings inside of a string_agg

Question

In PSQL I am aggregating concatenated strings from a table called genus_synonym

An example of the table is as follows

id|genus_synonym|specific_epithet_synonym
---|----------|-----------
1 | Acer | rubrum
2 | Acer | nigrum
3 | Betula | lenta
4 | Carya | ovata
5 | Carya | glabra
6 | Carya | tomentosa

here is an image of my table if that is easier

the code I am using is like this

Select

string_agg(CONCAT(CONCAT(s."genus_synonym"), ' ', s.specific_epithet_synonym), ', ')as syno

FROM

 "public"."synonyms" as s

The result is:
Acer rubrum, Acer nigrum, Betula lenta, Carya ovata, Carya glabra, Carya tomentosa

What I am trying to figure out is if it is possible to instead produce this:

Acer rubrum, A. nigrum, Betula lenta, Carya ovata, C. glabra, C. tomentosa

Basically I am wanting to abbreviate the genus name to a single letter with a period following it, for the second and additional time a genus is repeated.

Even if this is not possible it would be good to know this and then if there was another way I could go about solving this.

Also, it doesn't look like anyone is responding to my question. Is it not clear? I haven't been able to find anything like this being asked before. Please let me know what I can do to make this question better.

Answer 1

qry:

t=# with a as (
  select *,case when row_number() over (partition by genus_synonym) > 1 and count(1) over (partition by genus_synonym) > 1 then substr(genus_synonym,1,1)||'.' else genus_synonym end sh
  from s92
)
select string_agg(concat(sh,' ',specific_epithet_synonym),',')
from a;
                              string_agg
-----------------------------------------------------------------------
 Acer rubrum,A. nigrum,Betula lenta,Carya ovata,C. glabra,C. tomentosa
(1 row)

Time: 0.353 ms

mockup your data:

t=# create table s92 (id int,genus_synonym text,specific_epithet_synonym text);
CREATE TABLE
Time: 7.587 ms
t=# copy s92 from stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1 | Acer | rubrum
2 | Acer | nigrum
3 | Betula | lenta
4 | Carya | ovata
5 | Carya | glabra
6 | Carya | tomentosa
>> >> >> >> >> >> \.
COPY 6
Time: 6308.728 ms