DuckDB provides us with a good selection of functions for working with date and timestamp values. One such function is the LAST_DAY() function, which returns the last day of the month of the given date.
The way it works is, we pass a date to the function, and it returns the date of the last day of that month.
DuckDB provides us with a list_resize() function for those occasions where we need to resize a list. It also offers an alias called array_resize() that does exactly the same thing. When we resize a list using these functions, we specify how big we want the resulting list to be, and we can also specify a value to use for any extra elements that are added to the list.
DuckDB has a repeat() function that enables us to output a repeating value easily and concisely. The way it works is that we pass the value to the function, followed by how many times we want it to be repeated. It returns that value repeated the specified number of times.
In DuckDB, we can use the ISINF() function to check whether a value is finite. DuckDB supports infinite values, and so this function allows us to check for that. This can be useful when working with floating-point data that might contain special values like NaN (Not a Number) or infinity. The function also works on date and timestamp values, as they can be infinite too.
Occasionally we might need to filter data based on the existence of a given path in a JSON document in the database. When using DuckDB, we can achieve this outcome with the JSON_EXISTS() function. This function returns true or false, depending on whether the specified path exists. Therefore, we can use it to help us filter our query on that basis.
In DuckDB, we can use the json_contains() function to check whether a specified JSON value is contained within another JSON structure. It returns a boolean (TRUE or FALSE) that indicates whether or not the value was found. We can also check for key value pairs, or even a full JSON object within the JSON document.
This article shows you how to output your DuckDB query results in Markdown table format when using the DuckDB command line interface (CLI). This can be useful when creating documentation or preparing content for platforms that support Markdown.
DuckDB provides us with a good selection of functions for working with dates and timestamps. One of the things we might find ourselves needing to do is extracting the ISO weekday from a date or timestamp—a numeric value where Monday is represented as 1 and Sunday as 7.
This article presents four functions we can use to get the ISO weekday from a date in DuckDB.
If you’re getting an error that reads “AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY” in SQLite, it appears that you’re trying to define a column as an AUTOINCREMENT on a column that’s not defined as INTEGER PRIMARY KEY.
SQLite only allows us to use AUTOINCREMENT on INTEGER PRIMARY KEY columns.
To address this issue, be sure to make the column an INTEGER PRIMARY KEY if you need to use AUTOINCREMENT on it.
DuckDB has a arg_min_null() function that works in a similar way to the arg_min() function. That is, it finds the row with the minimum value in one column and returns the corresponding value from another column at that row.
But there’s also a difference between these two functions. The main difference is in the way they deal with NULL values. Also, arg_min_null() only accepts two arguments, whereas arg_min() accepts an optional third argument. Additionally, there aren’t any aliases for arg_min_null() at the time of writing (arg_min() has a couple of aliases).
In this article we’ll look at how arg_min_null() works, and we’ll compare it with arg_min() to see how each function handles NULL values.