22

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.

Bhawan
  • 2,441
  • 3
  • 22
  • 47

5 Answers5

22

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

Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
4

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
Brideau
  • 4,564
  • 4
  • 24
  • 33
3

Redshift has a listagg function you can use instead:

SELECT _bs, LISTAGG(_wbns, ',') FROM bag GROUP BY _bs;
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • 1
    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] ----------------------------------------------- – Bhawan Sep 19 '18 at 12:34
  • 1
    I am also having this issue where it says "Result size exceeds LISTAGG limit", does Redshift have any workaround for this. – Tejaswa Feb 07 '20 at 13:25
1

Use listagg function:

select _bs, 
listagg(_wbns,',')
within group (order by _bs) as val
from bag
group by _bs
Fahmi
  • 37,315
  • 5
  • 22
  • 31
0

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;
dburtsev
  • 1