This seems easy enough but I can't get the format correct. I have some rows like this:
alias # is character varying(20) column-type
------
Bob2
Judy5
Jane6
Erica7
I'd like to aggregate all these rows into 1 array:
[Bob2, Judy5, Jane6, Erica7]
I can only seem to get:
{(Bob2), (Judy5), (Jane6), (Erica7)}
SELECT array_agg(r) FROM (
SELECT name FROM mytable
) r
How can I get it to the format I'd like?
You are aggregating a set not a column (because r in your query refers to the derived table, not to a column). All you need to do is:
select array_agg(name)
from my_table;
Or if you insist on having the derived table:
SELECT array_agg(r.name)
FROM (
SELECT name
FROM mytable
) r
You could try this:
create table test(alias varchar(20));
insert into test values ('Bob2'), ('Judy5'), ('Jane6'), ('Erica7');
select replace(replace(array_agg(alias)::varchar, '{', '['), '}', ']') from test
Result:
[Bob2,Judy5,Jane6,Erica7]
