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 readingIf 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 readingIf 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.
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.
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.
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 readingIn PostgreSQL it’s possible to make an array larger without providing any values. For example, we can take an array with three elements, and enlarge it so that it contains say, five elements, with the two extra elements being NULL.
We can also do it so that some of the extra elements are non-NULL and others are NULL.
When we update an array in PostgreSQL, we can update one specific element or we can update a whole slice (a range of elements).
To update a slice, we use a syntax that references the lower and upper bounds of the slice, separated by a colon. We also provide the new values that will be assigned to each element within that slice.
Continue readingWhen we use the format() function in PostgreSQL, we can pass any number of strings to insert into the resulting formatted string in positions that are specified in the first argument. When we do this, we might typically pass the strings as separate arguments. But this isn’t the only way to do it.
We have the option of passing the strings as array elements. That is, we can pass an array to the function, and have it extract each array element as though it’s a separate argument to insert into the formatted string.
Continue readingPostgreSQL provides us with the ability to create arrays, extract element values from them, and more. When it comes to selecting values, there’s a special syntax that we must use in order to get the exact element that we want to select.
Basically, the syntax involves the array or column name, followed by a pair of square brackets with the subscript of the element that we want to select.
Continue reading