Fix “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.

Continue reading

Using LIST_REVERSE() to Reverse the Items in a List in DuckDB

Deep amongst DuckDB’s vast selection of list functions lies the list_reverse() function. This function reverses the elements in a list. We simply pass the list to the function and it reverses the order of the elements in that list.

Not to be confused with the list_sort() or list_reverse_sort() functions, the list_reverse() function reverses the order of the items as they currently sit in the list. The ...sort() functions on the other hand, actually perform a sort operation on the list items.

Continue reading

2 Ways to Return the Number of Rows Changed by a SQL Statement in SQLite

SQLite provides several ways to determine how many rows are affected by SQL statements such as INSERT, UPDATE or DELETE.

One way of achieving this is with the changes() function. This function returns the number of rows modified by the most recent SQL statement executed in the current session.

Another way is with the .changes dot command.

In this article, we’ll look at an example that uses these options to get the number of rows changed by various SQL statements.

Continue reading

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 reading

Unnesting Lists & Structs with DuckDB’s UNNEST() Function

DuckDB has an unnest() function that we can use to unnest lists and structs. Well, it can also be applied to NULL, but that’ll return an empty result.

By “unnest” I mean it takes the list or struct, and it returns its contents as rows in a table. You might say that it converts lists and structs into tables, where each item in the list or struct becomes a row in the table.

Below are examples of using DuckDB’s unnest() function to unnest lists and structs.

Continue reading