Alternatives of array_agg() or string_agg() on redshift

Question

I am using this query to get the aggregated results:

select _bs, string_agg(_wbns, ',') from bag group by 1;

I am getting this error:

Error running query: function string_agg(character varying, "unknown") does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts.

I also tried array_agg() and getting the same error.

Please help me in figuring out the other options I can use to aggregate the results.

Answer 1

you have to use listagg for reshift

For each group in a query, the LISTAGG aggregate function orders the rows for that group according to the ORDER BY expression, then concatenates the values into a single string.

LISTAGG is a compute-node only function. The function returns an error if the query doesn't reference a user-defined table or Amazon Redshift system table.

Your query will be as like below

select _bs, 
listagg(_wbns,',')
within group (order by _wbns) as val
from bag
group by _bs
order by _bs;

for better understanding Listagg

Answer 2

To get an array type back instead of a varchar, you need to combine the LISTAGG function with the SPLIT_TO_ARRAY function like so:

SELECT
  some_grouping_key,
  SPLIT_TO_ARRAY(LISTAGG(col_to_agg, ','), ',')
FROM some_table
GROUP BY 1

Answer 3

Redshift has a listagg function you can use instead:

SELECT _bs, LISTAGG(_wbns, ',') FROM bag GROUP BY _bs;

Answer 4

Use listagg function:

select _bs, 
listagg(_wbns,',')
within group (order by _bs) as val
from bag
group by _bs