Postgres Grouping of Like Results

Question

Our database is running on Postgres 9.3. We have a table called notifications.

Notifications contains id, type, triggering id, source id, and some other information.

What I am trying to do is select the notifications and then group them on their source ID.

So if I have the following:

id type source_id triggering_id
1 comment 1234 4567
2 comment 1234 0123
3 comment 1234 5432

I would like to get the following back:

source_id, triggering_id(s)

I don't know if this is even possible. I'm pretty new to the DBA stuff.

Answer 1

If I understand you correctly you want a comma separated list of triggering_id's

select source_id, 
 string_agg(triggering_id::text,',') as id_list
from notifications
group by source_id;

• Andrew Goldenberg: Sorry I suck horribly at explaining this stuff. Basically I have the notifications table and I need to return groups of like notifications instead of looping through and returning them 1 by 1.
• user1822: @AndrewGoldenberg If the query isn't returning what you want, you have to add the expected output based on your example data (edit your question)
• ypercubeᵀᴹ: @a_horse_with_no_name they may want just order by source_id, ..., without group by (i.e. rows with same source_id "grouped" together).
• user1822: @ypercube: the part "I would like to get the following back: source_id, triggering_id(s)" lead me to assume Andrew wants a comma separated list.
• ypercubeᵀᴹ: I know, I assumed the same thing and was going to answer when I saw yours ;)