If you ever find yourself in the situation where you need to count up the number of unique elements in a list in DuckDB, you’ll be happy to know that there’s a list_unique() function that does exactly that.
Tag: how to
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.
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.
Suppressing Query Output in the DuckDB CLI
DuckDB’s command-line interface (CLI) provides a way to suppress query results using the .mode trash command. This feature is particularly useful in scenarios where you need to execute queries but don’t want their results to be displayed.
Working with Intervals in DuckDB
Time-based operations are a fundamental aspect of data analysis and manipulation. DuckDB, the in-process analytical database management system, provides robust support for handling time-based operations through its INTERVAL data type.
This article explores the capabilities, syntax, and practical applications of intervals in DuckDB.
Continue reading2 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 readingFix “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 readingRemoving Duplicates from a List in DuckDB with the LIST_DISTINCT() Function
DuckDB has a function called list_distinct() that enables us to remove duplicate values from a list. Any value that appears more than once is “deduped” and is returned once in the resulting list.
The function also removes any NULL values.
Continue readingHow to Output Query Results as HTML Tables in the DuckDB CLI
The DuckDB command line interface (CLI) provides us with the ability to output query results in various formats. One of these formatting options is HTML.
This article shows you how to output your DuckDB query results as HTML tables, which can be useful when you need to include the results in web pages or documentation.
Continue reading4 Functions That Check Whether a List Contains a Given Value in DuckDB
When working with DuckDB, we may need to determine whether a specific value exists within a list or array. Fortunately, DuckDB offers four synonymous functions that enable us to accomplish this.
Continue reading