Postgresql aggregate array bracket removal

Question

How do i remove the brackets {} from an Array_AGG query output?

ARRAY_AGG(DISTINCT(SGL.short_name))

from this {01,02} to this 01|02|

thanks!

Craig Ringer · Accepted Answer · 2015-08-21T05:50:41.857

9

Use string_agg instead.

string_agg(DISTINCT SGL.short_name, '|')

edited Aug 21 '15 at 05:50

answered Aug 21 '15 at 03:16

Craig Ringer

score 6 · Answer 2 · answered Aug 21 '15 at 01:53

6

Use array_to_string():

ARRAY_TO_STRING(ARRAY_AGG(DISTINCT(SGL.short_name)), '|')

answered Aug 21 '15 at 01:53

Gordon Linoff

Is it possible to change the order of the output from 01|02|03|04|05|KG|PK to PK|KG|01|02|03|04|05 ? – user3263892 Aug 21 '15 at 14:10
@user3263892 . . . Yes, you would add an `order by` clause. – Gordon Linoff Aug 22 '15 at 03:40

2 Answers2