Postgres array_agg each value at new string within one row

Question

I have such a query

SELECT group_id, array_agg(element_id) FROM table
GROUP BY group_id;

As a result I have something like that:

    group_id | array_agg

    106      | {2147,2138,2144}
    107      | {2132,2510,2139}

What query should be written, so result may be depicted in this way:

        group_id | array_agg

        106      | {2147
                 |  2138
                 |  2144}
        107      | {2132
                 | 2510
                 | 2139}

klin · Accepted Answer · 2017-08-27 18:53:03Z

2

Basically one should format the output in a client app, however you can use string_agg() with a new-line character:

select group_id, string_agg(element_id::text, e'\n') 
from my_table
group by group_id;

 group_id | string_agg 
----------+------------
      106 | 2147      +
          | 2138      +
          | 2144
      107 | 2132      +
          | 2510      +
          | 2139
(2 rows)

answered Aug 27, 2017 at 18:53

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres array_agg each value at new string within one row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related