Partial indexing is a feature in many DBMSs, including SQLite, that allows developers to optimize database performance by creating indexes only for specific subsets of data. This can significantly reduce the index size and improve query speed, especially in cases where only a portion of the data is frequently queried.
In this article, we’ll look at how to create a partial index in SQLite, why it’s useful, and provide an example to illustrate its use.
Continue readingIf 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.
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 readingDuckDB’s command line interface (CLI) provides a convenient .mode csv command that allows you to output all query results directly in CSV format. This approach differs from the COPY statement as it affects the output format of all subsequent queries until changed.
The article takes a quick look at CSV mode in the DuckDB CLI.
Continue readingDuckDB is a database management system tailored for analytical tasks. Its command line interface (CLI) provides a convenient way to interact with databases and execute queries. The CLI provides us with the ability to configure the output mode for query results, offering flexibility in how data is displayed or exported.
This article provides a quick overview of how to change the output mode in the DuckDB CLI.
Continue readingDuckDB is a lightweight, high-performance database system designed for analytical workloads. As with any database management system (DBMS), one of the first things we normally want to do when we launch it is to create or open a database.
When it comes to creating a database, you can’t create a database in DuckDB by using the SQL CREATE DATABASE statement (unless you’re using a tool that allows you to do so). DuckDB works differently.
In this article, we look at various options for creating a database in DuckDB.
Continue readingDuckDB is a high-performance in-process analytical database management system, often referred to as the “SQLite for analytics.” Its Command Line Interface (CLI) is a useful tool for executing SQL queries, managing databases, and exploring DuckDB’s features.
This article provides a comprehensive guide to the basics of using DuckDB’s CLI, helping beginners and advanced users alike understand how to leverage it effectively.
Continue reading