When using DuckDB’s command line interface (CLI), we can use the .mode command to change how query results are formatted. For example, we can output query results as a table, in CSV format, or even JSON. Another option is to output it in “line” mode, which outputs the query results vertically, as opposed to horizontally across the screen.
This article demonstrates how to enable vertical query output in the DuckDB CLI with line mode.
In DuckDB, the CONCAT_WS() function provides an efficient way to join strings with a specified separator. CONCAT_WS() stands for “concatenate with separator”, and many RDBMSs have such a function.
CONCAT_WS() is particularly useful when you need to combine multiple fields or values with a consistent delimiter.
Let’s explore its features and practical applications.
Continue readingString concatenation is a common operation in database queries, and many database management systems (DBMSs) provide at least one or two ways to concatenate strings. DuckDB is no exception.
One option for concatenating strings in DuckDB is with the CONCAT() function. This function provides a robust and NULL-safe way to combine strings.
Unlike the concatenation operator (||), CONCAT() handles NULL values gracefully and provides a cleaner syntax for combining multiple strings.
In this article, we’ll explore everything from basic usage to advanced techniques and best practices.
Continue readingDuckDB is a fast and reliable analytical database that offers a bunch of aggregate functions. One such function is ANY_VALUE().
In this post, we’ll explore the ANY_VALUE() function, along with examples to demonstrate its usage.
If you’re getting an error that reads “mode should be one of: ascii box column …etc” in DuckDB, it appears that you’re trying to set the output mode to an invalid value.
While we can change the output mode in DuckDB, it must be one of the supported modes.
To fix this issue, be sure to use a supported mode.
Continue readingDuckDB’s CLI provides a convenient way to generate INSERT statements from query results. This can be particularly useful for data migration, creating test data, or backing up specific data subsets. Here’s how to do it.
NULL values in databases can be tricky to handle and often lead to unexpected results. DuckDB’s COALESCE() function provides an easy way to manage NULL values effectively.
In this article, we’ll explore the COALESCE() function in DuckDB with some practical examples.
If you’ve ever run .help mode in the DuckDB command line interface (CLI), you may have noticed that it returns information about the .mode command, including its syntax.
You might’ve noticed that there’s a ?TABLE? part in the syntax. What’s this about? Maybe you’ve tried it out and found that it didn’t appear to do anything?
In this article, I’ll explain what the ?TABLE? part is for, and I’ll run an example to demonstrate.
DuckDB is a lightweight, fast database management system designed for analytics and embedded use cases. Its versatility makes it an excellent choice for developers and data analysts.
One useful feature of DuckDB is the ability to output query results in different formats, such as JSON, directly from the command-line interface (CLI). By default, the DuckDB CLI uses the duckbox output mode for query results (which outputs the results in a table-like format), but we can change that.
In this article, we’ll walk through the steps to output query results as JSON when using the DuckDB CLI.
Continue readingDuckDB is a fast, efficient, and embeddable SQL database designed for analytical workloads. As with any other RDBMS, a common task is listing all the tables in your DuckDB instance.
This article covers five options for displaying a list of tables in DuckDB.
Continue reading