Difference between string_agg vs array_agg in PostgreSQL?

Question

I think string_agg vs array_agg is almost same when one is return string type and another is return array type. Is there any other difference between them?

Which should I prefer? First or second?

array_agg(tag_name, ',') as Tag

OR

string_agg(tag_name, ',') as Tag

OR

array_to_string(array_agg(tag_name), ',') as Tag

Can anyone explain it for me? 1.array_agg 2. string_agg 3. array_to_string

Answer 1

Your first expression stems from an era where Postgres didn't have string_agg() and shouldn't be used if you simply want a string (text) value.

Using string_agg(tag_name, ',') is the preferred way with modern Postgres versions if you want to concatenate string values.

---

You use string_agg() when you want a string value, and array_agg() when you want to work with an array.