Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
An example of using the array_agg() PostgreSQL function.
select
pc_games.title,
to_char(pc_games.release_date, 'YYYY-MM-DD') as release_date
array_to_string(array_agg(avid_fans.firstname || ' ' || avid_fans.surname), ', ', null) as fans
from
schema_A.pc_games as pc_games
inner join schema_A.avid_fans as avid_fans on (pc_games.id = avid_fans.fk_pc_game_id)
where
pc_games.release_date > '2004-12-31'
group by
pc_games.title,
pc_games.release_date,
avid_fans.firstname,
avid_fans.surname
-- Here we are selecting the particulars of all PC games released since 2016 along with all their fans.
-- Because each PC game likely has more than one fan, we are going to see a lot of repeat data.
-- Each record will contain the details of the PC game along with the name and surname of one fan.
-- If a game has ten fans we are going to see the same game's details ten times folowed by each fan's name & surname.
-- array_agg will place all the fans' names and surnames in a single field separated by commas.
-- So instead of returning:
-------------------------------------------------------------------------
-- title | release_date | fans --
-------------------------------------------------------------------------
-- Prince of Persia: The two thrones | 2005-06-15 | Pierre Joubert --
-- Prince of Persia: The two thrones | 2005-06-15 | Daniel France --
-- Prince of Persia: The two thrones | 2005-06-15 | Mariska Etsebeth --
-------------------------------------------------------------------------
-- The query will return:
----------------------------------------------------------------------------------------------------------
-- title | release_date | fans --
----------------------------------------------------------------------------------------------------------
-- Prince of Persia: The two thrones | 2005-06-15 | Pierre Joubert, Daniel France, Mariska Etsebeth --
----------------------------------------------------------------------------------------------------------
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.