By default, when a query returns a null value in the DuckDB command line interface (CLI), an empty string is displayed. This may or may not be what you want. But if you’re like me, you probably want DuckDB to explicitly tell you that it’s a null value. After all, if an empty string is returned, perhaps the data contained an empty string?
Fortunately, DuckDB provides us with the .nullvalue dot command so that we can change the output of null values.
DuckDB provides us with a bunch of functions that we can use to return the current date and/or time. We can get the date in local time or coordinated universal time (UTC), depending on the function we use. These can be useful in a range of scenarios, such logging the current date in a database column, or filtering or comparing dates based on the current date.
Continue readingIf you’re getting an error that reads “Error: Month out of range, expected a value between 1 and 12” when using the strptime() function in DuckDB, it could be that you’re getting your months mixed up in your format string.
Being able to ensure uniqueness in database tables is critical for maintaining data integrity. SQLite allows us to create unique indexes in order to enforce uniqueness, and it provides us with tools to check the unique indexes associated with a table.
This article outlines how to use the SQLite PRAGMA index_list() command to check a table for unique indexes.
When working with dates in DuckDB, extracting specific components like the day is a common task. DuckDB provides a good range of functions that we can use to help us to perform such a task.
This article presents seven different functions we can use to extract the day from a date in DuckDB.
Continue readingWhen working with SQL databases such as DuckDB, dates usually conform to a specific format. In particular, when we create dates in DuckDB, they must conform to the ISO 8601 format (YYYY-MM-DD). It’s the same with time (hh:mm:ss[.zzzzzz][+-TT[:tt]]) and timestamp values (YYYY-MM-DD hh:mm:ss[.zzzzzzzzz][+-TT[:tt]]).
But what if we have a requirement to present these dates or timestamps in a different format?
Fortunately, DuckDB provides us with tools to so. This article explains how to format date and timestamp values according to a specified format.
Continue readingSometimes we need to subtract a certain time interval from a date when working with DuckDB. We might want to subtract hours, minutes, days, months, or even years.
Below are two methods we can use to subtract one or more years from a date in DuckDB.
Continue readingIf you’re getting an error that reads “Binder Error: APPROXIMATE QUANTILE can only take parameters in range [0, 1]” in DuckDB, it appears that you’re passing an invalid second argument to the approx_quantile() function.
To fix this issue, make sure the second argument is between 0 and 1.
Continue readingAs an Azure Data Studio user, I was surprised when an alert popped up informing me that Azure Data Studio (ADS) is being retired on February 28, 2026. The message included a link to Microsoft’s recent announcement.
Microsoft now recommends we transition to Visual Studio Code (VS Code) instead.
Continue readingIf you find yourself running a lot of ad-hoc queries that start with SELECT * FROM, you might be interested in this interesting feature of DuckDB.
DuckDB allows us to start queries with the FROM clause. When we do this, DuckDB will return all columns without us needing to specify SELECT *.