If you’re getting a binder error that reads something like “No function matches the given name and argument types ‘list_concat(STRING_LITERAL, VARCHAR[][])’. You might need to add explicit type casts.” in DuckDB when using either the array_push_front() or array_push_back() functions, it could be due to a slight syntax error.
Tag: how to
3 Ways to Add the Numbers in a List in DuckDB
If you ever have a list of numbers in DuckDB that you want to add up, there are several ways to do this. By “add up” I mean, say you have a list that contains three numeric values. You can use the following methods to get a sum of all those values.
Continue reading2 Ways to Check if a Table has any Partial Indexes in SQLite
In SQLite, a partial index includes only the rows that meet a specified condition, set by a WHERE clause in the index definition. This is different from a regular, or “full,” index, which covers all rows in a table. Partial indexes are helpful when you frequently query specific subsets of data, as they can improve query performance and reduce storage.
If you ever need to check a table to see if it contains any partial indexes, you can use either of the following methods.
Continue readingCheck for Overlapping Elements Across Lists in DuckDB: LIST_HAS_ANY()
DuckDB provides us with the list_has_any() function that enables us to find any overlapping elements across two lists. The way it works is that we pass two lists, and it returns true or false, depending on whether any element exists in both lists.
4 Ways to Get the ISO Year from a Date in DuckDB
In the ISO-8601 calendar, years begin on the first Monday closest to January 1, meaning the start date can fall between December 29 and January 4. This differs from the Gregorian calendar, where years always start on January 1, leading to potential discrepancies around these dates. Additionally, ISO-8601 years, or just ISO years, can be either 52 or 53 weeks long, depending on their starting point.
This article provides four options for extracting the ISO year from a date in DuckDB, accounting for these unique calendar rules.
Continue readingFix “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.
Subtracting Minutes from a Date/Time Value in DuckDB
DuckDB provides us with the means to add and subtract intervals to/from date, timestamp, and time values. We can perform subtraction with the minus (-) operator or the date_add() function (along with the minus operator).
Below are examples of using each method to subtract minutes from date/time values.
Continue readingHow to Output Query Results as Pipe-Separated Lists in the DuckDB CLI
This tutorial walks you through outputting DuckDB query results in list format, which presents each record as a pipe-separated list of values. This format is handy for data processing tasks and when working with tools that expect pipe-delimited input.
Continue readingUsing LIST_UNIQUE() to Count the Unique Elements of a List in DuckDB
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.
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.