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.
4 Functions to Get the ISO Weekday in DuckDB
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.
Fix Error “AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY” in SQLite
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.
Understanding DuckDB’s ARG_MIN_NULL() Function
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.
Dealing with Different Date Formats When Using STRPTIME() in DuckDB
In DuckDB, the strptime() function converts a date/time string into a valid timestamp value. We pass a format string to the function in order to tell it what format our string uses. This can be handy if we ever need to construct timestamps based on date/time strings that may or may not be in a valid format.
But what if we have multiple date/time strings in different formats?
Fortunately, the strptime() function caters for this scenario too.
5 Ways to Get the Data Type of Columns Output by a Query in DuckDB
Usually when we run a SQL query it’s important to know the data type of the columns returned by that query. If we’re familiar with the data, then we might instinctively know the data type of each column. But even then, we might only have a general idea, such as “it’s a date” or “it’s a number” without knowing the exact details.
Fortunately, DuckDB provides us with several ways to find out the data type of the columns returned by a query.
LIST_REVERSE() vs LIST_REVERSE_SORT() in DuckDB: What’s the Difference?
DuckDB has a good range of functions for dealing with lists and arrays. Amongst these are list_reverse() and a list_reverse_sort(). Looking at their names, you could be forgiven for thinking that these do the same thing. But they don’t.
If you’re wondering why DuckDB has a list_reverse() and a list_reverse_sort() function, read on.
Convert Column Values to a JSON Array with DuckDB’s JSON_GROUP_ARRAY() Function
The json_group_array() function in DuckDB is used to aggregate values into a JSON array, making it especially useful when working with structured or semi-structured data such as JSON. This function is part of DuckDB’s JSON extension and works similarly to DuckDB’s string_agg() or the group_concat() function in some other RDBMSs, but instead of returning a delimited string, it returns a well-formed JSON array.
This function is particularly helpful when we need to represent grouped or hierarchical data in a JSON format for export, reporting, or further transformation.
An Overview of the TODAY() Function in DuckDB
DuckDB has a today() function that returns the current date. It’s similar to the current_date function, which does the same thing.
Here, we’ll look at how the today() function works, along with some basic examples.
Subtract Hours from a Date/Time Value in DuckDB
When working with SQL databases, one operation we often find ourselves performing is date/time arithmetic, such as adding or subtracting an interval to/from a date/time value. Fortunately, most RDBMSs make such operations quite easy to achieve, and DuckDB is no exception.
Below are two methods we can use in order to subtract hours from a date, timestamp, or time value in DuckDB.