{"id":21523,"date":"2021-11-29T07:41:59","date_gmt":"2021-11-28T21:41:59","guid":{"rendered":"https:\/\/database.guide\/?p=21523"},"modified":"2021-11-29T07:41:59","modified_gmt":"2021-11-28T21:41:59","slug":"postgresql-group_concat-equivalent","status":"publish","type":"post","link":"https:\/\/database.guide\/postgresql-group_concat-equivalent\/","title":{"rendered":"PostgreSQL GROUP_CONCAT() Equivalent"},"content":{"rendered":"\n

Some RDBMSs like MySQL and MariaDB have a GROUP_CONCAT()<\/code> function that allows you to return a query column as a delimited list (for example, a comma separated list).<\/p>\n\n\n\n

PostgreSQL has a similar function called STRING_AGG()<\/a><\/code>. This function works in pretty much the same way that GROUP_CONCAT()<\/code> works in MySQL and MariaDB.<\/p>\n\n\n\n\n\n\n\n

Example<\/h2>\n\n\n\n

Here’s a basic example to demonstrate how PostgreSQL’s STRING_AGG()<\/code> function works:<\/p>\n\n\n\n

SELECT STRING_AGG(genre, ',') FROM Genres;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Rock,Jazz,Country,Pop,Blues,Hip Hop,Rap,Punk<\/pre>\n\n\n\n
Here’s what happens when we just do the query without the STRING_AGG()<\/code> function:<\/p>\n\n\n\n
SELECT genre FROM Genres;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+---------+\n|  genre  |\n+---------+\n| Rock    |\n| Jazz    |\n| Country |\n| Pop     |\n| Blues   |\n| Hip Hop |\n| Rap     |\n| Punk    |\n+---------+\n(8 rows)<\/pre>\n\n\n\n
We get eight rows, each with a separate value, instead of one long comma separated row of all values.<\/p>\n\n\n\n
If anything, PostgreSQL’s STRING_AGG()<\/code> is more like MySQL’s GROUP_CONCAT()<\/code> than MariaDB’s function of the same name. I say this because MariaDB’s GROUP_CONCAT()<\/code> allows us to provide a LIMIT<\/code> clause (as from MariaDB 10.3.3), right from within the function itself. MySQL GROUP_CONCAT()<\/code> doesn’t support the LIMIT<\/code> clause, and neither does PostgreSQL’s STRING_AGG()<\/code> function (at least, not at the time of this writing).<\/p>\n\n\n\n
Another difference is that PostgreSQL’s STRING_AGG()<\/code> requires a second argument (that specifies the delimiter to use). Both MySQL and MariaDB make this optional with their GROUP_CONCAT()<\/code> functions.<\/p>\n\n\n\n
Postgres’s STRING_AGG()<\/code> accepts an ORDER BY<\/code> clause, and a DISTINCT<\/code> clause (as do MariaDB and MySQL’s GROUP_CONCAT()<\/code> functions).<\/p>\n\n\n\n
See STRING_AGG()<\/code> Function in PostgreSQL<\/a> for more examples.<\/p>\n\n\n\n
And in case you’re interested, SQL Server also has a STRING_AGG()<\/code> function<\/a> that works in pretty much the same way.<\/p>\n","protected":false},"excerpt":{"rendered":"
Some RDBMSs like MySQL and MariaDB have a GROUP_CONCAT() function that allows you to return a query column as a delimited list (for example, a comma separated list). PostgreSQL has a similar function called STRING_AGG(). This function works in pretty much the same way that GROUP_CONCAT() works in MySQL and MariaDB.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[40],"tags":[73,113,93,10],"class_list":["post-21523","post","type-post","status-publish","format-standard","hentry","category-postgresql","tag-aggregate-functions","tag-aggregation","tag-functions","tag-how-to"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/21523","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=21523"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/21523\/revisions"}],"predecessor-version":[{"id":21537,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/21523\/revisions\/21537"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=21523"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=21523"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=21523"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}