DuckDB provides us with a few functions that prepend a value to a list. Actually, these functions are synonymous, so they all do the same thing, with the same/similar basic syntax.
Continue readingTag: arrays
Fix “Macro function generate_subscripts(arr, dim) requires 2 positional arguments” in DuckDB
If you’re getting an error that reads something like “Macro function generate_subscripts(arr, dim) requires 2 positional arguments” in DuckDB, it appears that you’re calling the generate_subscripts() function with the wrong number of arguments.
Understanding DuckDB’s LIST_GRADE_UP() Function
Among DuckDB’s many tools for handling list data is the list_grade_up() function. This function works similarly to a sort operation, but instead of returning the sorted values themselves, it returns the indexes that represent the positions of those values in the original list.
Let’s take a quick look.
Continue readingFix “No function matches the given name and argument types” When Using GENERATE_SUBSCRIPTS() in DuckDB
If you’re getting an error that reads something like “No function matches the given name and argument types” when using the generate_subscripts() function in DuckDB, it could be that your first argument is not an array. Or it could be that your second argument is not compatible with the INT64 type.
Fix “Out of Range Error” When Using GENERATE_SUBSCRIPTS() in DuckDB
If you’re getting an “Out of Range Error” when using the generate_subscripts() function in DuckDB, it could be that you’re specifying a non-existent dimension for the array.
DuckDB’s generate_subscripts() function accepts the array as the first argument, and the dimension as the second argument. The second argument must correspond to an actual dimension present in the array.
To fix this issue, be sure to specify a dimension that actually exists in the array.
Continue readingFix ‘Binder Error: Could not find key “…” in struct’ When Using DuckDB’s ARRAY_EXTRACT() Function
If you’re getting an error that reads something like “Binder Error: Could not find key “…” in struct” when using the array_extract() function in DuckDB, it could be that you’re specifying a non-existent key.
When using DuckDB’s array_extract() function to extract a value from a struct, we must provide a key that actually exists in the struct. Otherwise we’ll end up with an error like the above one.
To fix this issue, be sure to specify a key that actually exists in the struct.
Continue readingFix “Could not convert string ‘…’ to INT64” When Using the GENERATE_SUBSCRIPTS() Function in DuckDB
If you’re getting an error that reads something like “Could not convert string ‘…’ to INT64” when using the generate_subscripts() function in DuckDB, it appears that your second argument is a string, when it should be an integer.
DuckDB’s generate_subscripts() function accepts two arguments; the array as the first argument, and the dimension as the second argument. The second argument must be INT64 (or be able to be implicitly converted to that type). Passing the wrong data type as the second argument can cause the above error to occur.
To fix this issue, make sure that the second argument is compatible with INT64.
Looking at the GENERATE_SUBSCRIPTS() Function in DuckDB
The generate_subscripts() function in DuckDB creates a series of index values for accessing elements in an array. It can help us iterate through array indices when we need to work with array elements.
Here’s a quick look at DuckDB’s generate_subscripts() function, along with examples.
Exploring ARRAY_AGG() in DuckDB
array_agg() is an aggregate function in DuckDB that allows you to combine values from multiple rows into a list. This article explores how array_agg() works, along with some examples that demonstrate its usage.
Concatenate Array Elements into a String in PostgreSQL
You may be aware that PostgreSQL has a couple of functions that allow us to concatenate strings. In particular, the concat() function allows us to concatenate multiple strings into one string, and the concat_ws() function allows us to do the same, but to also specify a separator for the concatenated strings.
But did you know that we have the ability to pass an array to these functions?
Continue reading