In this article we look at how to automatically enclose values in single quotes when outputting DuckDB query results as a list. Using this method, each value is formatted as SQL literals. This can be useful when you want to escape the values for SQL or for some other purpose.
We also look at how CSV output is sometimes quoted with double quotes, and how we can adjust the results by doing things like removing the headers and changing the list separator.
Continue readingIf you’re using DuckDB and you need to check which version you’re running, here are some quick and easy methods to do so.
Continue readingPerforming date/time arithmetic is a common operation when using SQL databases such as DuckDB. As with most RDBMSs, DuckDB makes these types of operations relatively straightforward for us.
Here are two options for adding one or more seconds to a date, timestamp, or time value.
Continue readingDuckDB’s SAMPLE clause is a handy feature that allows us to work with a random subset of our data. This is particularly useful when dealing with large datasets where processing the entire dataset might be time-consuming or unnecessary for exploratory data analysis, testing queries, or creating representative samples.
When we use this clause, we can specify the absolute number of rows to return, or a percentage of rows. We also have an option of sampling method to use.
Continue readingThe range() function is a handy utility in DuckDB that enables us to generate sequences of numbers or timestamps. We specify the starting point, the end point, and the step to use for each value in the range.
In this article, we’ll take a look at DuckDB’s range() function, along with some basic examples.
One of SQLite’s unique features is the WITHOUT ROWID table, which can be used to optimize performance and storage in specific scenarios.
While it’s easy enough to create a WITHOUT ROWID table (just add WITHOUT ROWID to the definition), how to identify a WITHOUT ROWID table might not be so obvious.
In this article, we’ll start by briefly revising what WITHOUT ROWID tables are and how they differ from ordinary tables. Then we’ll look at how to identify these tables by using SQLite’s PRAGMA commands.
The generate_subscripts() function in DuckDB creates a series of index values for accessing elements in an array. It can help us iterate through array indices when we need to work with array elements.
Here’s a quick look at DuckDB’s generate_subscripts() function, along with examples.
If you’re getting an error that reads “Conversion Error: Could not convert …etc” while using the COALESCE() function in DuckDB, it appears that you’re using arguments with incompatible types.
To fix this issue, try using CAST() or TRY_CAST() to ensure that all arguments are compatible. Alternatively, make sure the arguments to COALESCE() are of the same type (or at least, compatible types).
DuckDB is an in-process SQL OLAP database management system designed for analytical workloads. Among its various aggregate functions is the product() function, which calculates the product of a set of numeric values.
In this article, we’ll explore how the product() function works with some basic examples.
If you’ve ever used the strptime() function to create a timestamp in DuckDB, you may be aware that it will return an error if it can’t construct the timestamp from the format string/s provided.
While such an error could be useful in some situations, it could also be annoying in others.
Fortunately, DuckDB also provides the try_strptime() function, which will suppress any error that we might ordinarily get in such cases. This function returns null instead of an error.