concat_ws and list aggregate about duckdb HOT 7 CLOSED

I agree with both, but both are vestiges of older SQL systems and duckdb makes an effort to be compatible with those.

from duckdb.

This is as expected. You're calling concat_ws(x, y) and are getting y::VARCHAR. You seem to think you're calling concat_ws(x, y[0], ..., y[n])

from duckdb.

To get what you want, you could use list_reduce(list(...), (x, y) -> concat_ws(', ', x, y)) although I feel like there might be a more elegant solution. As far as I'm aware there is no unpacking operator (like Python's star operator) though.

from duckdb.

Perhaps array_to_string()

from duckdb.

@cmdlineluser Ah, nice. TIL:

• All aggregate functions can be used on lists via list_aggregate(listValue, '<NAMEOFAGGREGATEFUNCTION>', <MOREARGSTOAGGREGATEFUNCTION>). For most aggregate functions this is also available under the shorthand list_<NAMEOFAGGREGATEFUNCTION>. See https://duckdb.org/docs/sql/functions/nested#list-aggregates
• There is a regular aggregate function called string_agg(..., sep). See https://duckdb.org/docs/sql/aggregates.html. It's one of the aggregates that has a list shorthand: list_string_agg(..., sep) (like all aggregate functions it can also be called via list_aggregate(..., 'string_agg', sep) on lists)
• For some reason, list_string_agg is also available as array_to_string

from duckdb.

Thanks all for prompt explanations and suggestions. I ended up using string_agg, which works.

I don't think I like to variadic concat_ws and would prefer concat_ws(sep, list) with SQL engine complaining if the second argument is not a list. Also automatic coercion to string is error-prone.

from duckdb.

I see. Then it's working as intended I guess.

from duckdb.