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.

Heterosexual

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

Fonzie

group by 1 will also work as it represents the first column of the query. – Heterosexual

Why this order by is required ? – Heterosexual

@BhawandeepSingla so that it added values in a order – Fonzie

it is required as it will aggregate _bs wise. @BhawandeepSingla – Escallop

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

Bead

will mess your result if your column value contains , – Situation

Or worse, if your "column" needs to be a record or struct with multiple fields. :( – Readus

Answer 3

Redshift has a listagg function you can use instead:

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

Lynd

First of all, it should be LISTAGG and other thing is I am getting this error on running the query: Error running query: Result size exceeds LISTAGG limit DETAIL: ----------------------------------------------- error: Result size exceeds LISTAGG limit code: 8001 context: LISTAGG limit: 65535 query: 895057 location: 0.cpp:695 process: query8_55_895057 [pid=29012] ----------------------------------------------- – Heterosexual

I am also having this issue where it says "Result size exceeds LISTAGG limit", does Redshift have any workaround for this. – Stendhal

Answer 4

Use listagg function:

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

Escallop

ERROR in query: Error running query: syntax error at or near "_bs" LINE 3: within group (order _bs) as val ^ – Heterosexual

hmm, by was missing after order - modified it – Escallop

Answer 5

Got Error:One or more of the used functions must be applied on at least one user created tables. Examples of user table only functions are LISTAGG, MEDIAN, PERCENTILE_CONT, etc

SELECT   refc.constraint_name,   refc.update_rule,   refc.delete_rule,   kcu.table_name,   
LISTAGG(distinct kcu.column_name, ',') AS columns   
FROM   information_schema.referential_constraints AS refc,   
information_schema.key_column_usage AS kcu
WHERE   refc.constraint_schema = 'abc'   AND refc.constraint_name = kcu.constraint_name   AND refc.constraint_schema = kcu.table_schema   
AND kcu.table_name = 'xyz' 
GROUP BY   refc.constraint_name,   refc.update_rule,   refc.delete_rule,   kcu.table_name;

Mcclelland