As with most other database management systems (DBMSs), DuckDB provides us with a way of finding the position of an element in a list. This is the index at which the specified element occurs in the list.
Actually, DuckDB provides us with four synonymous functions for doing this (i.e. they all do the same thing).
DuckDB provides us with a couple of ways to get the weekday name from a date or timestamp value. This could be the full day name, such as Monday, or the short/abbreviated day name, such as Mon.
Here are two ways to return the day name from a date in DuckDB.
DuckDB offers two primary functions for type conversion: cast() and try_cast(). While they serve similar purposes, their behavior when handling invalid conversions differs significantly, which can greatly impact our data processing workflows.
DuckDB has a json_keys() function that we can use to extract the keys from a JSON object. It returns the keys as a list of strings (LIST of VARCHAR).
Most SQL developers are familiar with the min() function that allows us to get the minimum value from a data set. But if we want to get the minimum value from a list, passing the list to the min() function won’t quite cut it. But don’t despair! Finding the minimum value in a list is just as easy. Here are three ways to do it. And yes, we can even use the min() function if we want.
If you’ve ever used list_concat() or any of its aliases to concatenate lists in DuckDB, you may have been disappointed to find out that it only concatenates two lists. Any more than two lists and it returns an error. At least that’s how it works at the time of this writing.
Fortunately, there are some alternatives we can use in order to concatenate three or more lists.
Here are four ways to concatenate three or more lists in DuckDB.
DuckDB has emerged as a powerful analytical database system designed for fast in-memory data processing. One common analytical task involves calculating time differences between events—whether measuring intervals between transactions, tracking service response times, or analyzing temporal patterns in time series data.
This article provides an exploration of calculating time differences in DuckDB, covering both basic and slightly more advanced techniques.
You may be aware that DuckDB includes a typeof() function that works just like the SQLite equivalent; it returns the data type of its argument. But did you know that DuckDB also provides us with a pg_typeof() function that does essentially the same thing?
So why would DuckDB need a pg_typeof() function that does basically the same thing as typeof()? Let’s find out!
DuckDB has a list_where() function that returns a list after a user-supplied mask has been applied. We pass the list as the first argument, and a list of Boolean values as the second. The list of Boolean values is applied as a mask to the list, which determines which values in the list are returned.
DuckDB provides us with a couple of easy ways to perform additions and subtractions on dates. In particular, we can use the - operator to do the job, or the date_add() operator combined with the - operator.
Here are two options for subtracting days from a date in DuckDB.