Not registered yet?
Register now! It is easy and done in 1 minute and gives you access to special discounts and much more!
I'm having a table like this
I want to get the name of a movie and all actors in that movie, and I want the result to be in a format like this:
A 1, 2, 3
How can I do it?
Simpler with the aggregate function string_agg() (Postgres 9.0 or later):
SELECT movie, string_agg(actor, ', ') AS actor_list
GROUP BY 1;
The 1 in GROUP BY 1 is a positional reference and a shortcut for GROUP BY movie in this case.
GROUP BY 1
GROUP BY movie
string_agg() expects data type text as input. Other types need to be cast explicitly (actor::text) - unless an implicit cast to text is defined - which is the case for all other character types (varchar, character, "char"), and some other types.
As isapir commented, you can add an ORDER BY clause in the aggregate call to get a sorted list - should you need that. Like:
SELECT movie, string_agg(actor, ', ' ORDER BY actor) AS actor_list
GROUP BY 1;
But it's typically faster to sort rows in a subquery. See:
I didn't know Postgres supported positional column references like that, and can't think of any good reason to use them, but otherwise this is spot on.
@IMSoP: It's just a syntactical convenience I slipped in. A good use case would be a complex expressions in the SELECT list or with dynamic SQL.
Small note - might need to actor::TEXT if actor is an INT. At least, I get an error trying to string_agg INTs in Postgres 9.5 - but otherwise, this was exactly what I needed, thanks!
@dwanderson: I added a note accordingly.
The above works but I get (...) on longer strings :(
@Chris: Probably an issue with your client settings, unrelated to the query. Consider: stackoverflow.com/a/23568429/939860
Worth noting that an optional ORDER BY clause can go into the string_agg function after the delimiter argument, e.g. string_agg(actor, ', ' ORDER BY actor DESC)
string_agg(actor, ', ' ORDER BY actor DESC)
late comment but a quick note on this nice, succinct solution. I found that this was actually consistently slower than the solution from @hims056 for my use case. (avg. 280ms vs 200ms). I'm a SQL/PostgreSQL novice and haven't been able to set up a SQLFiddle or similar, I'm afraid. Do you know of any obvious reason?
dear @ErwinBrandstetter, is there possibility to transform second value not as string but as a json?
@Pyrejkee: Certainly. Here are a couple of options: stackoverflow.com/a/26486910/939860
You can use array_agg function for that:
array_to_string(array_agg(distinct "Actor"),',') AS Actor
GROUP BY "Movie";
| MOVIE | ACTOR |
| A | 1,2,3 |
| B | 4 |
For more See 9.18. Aggregate Functions