Enable Vertical Query Output in DuckDB

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.

Continue reading

How CONCAT_WS() Works in DuckDB

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 reading

An Overview of the CONCAT() Function in DuckDB

String 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 reading

How to Create a Partial Index in SQLite

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 reading

Understanding SQLite’s CAST() Expression

SQLite is a widely-used lightweight database engine that powers many mobile, embedded, and desktop applications. One of its key features is its dynamic type system, which allows flexibility in handling data. While this is advantageous in many scenarios, there are cases where developers need precise control over how data is treated or converted between types. This is where SQLite’s CAST() expression comes in.

Continue reading