4

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

Suman Kumar Dash
  • 681
  • 5
  • 19

2 Answers2

3

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.

1

On your after-edited question, the first one throws an ERROR so you don't want that.

The 2nd and 3rd do the same thing, but the 3rd is gratuitously slow and memory intensive.

jjanes
  • 37,812
  • 5
  • 27
  • 34