In SQL Server, Common Table Expressions (CTEs) are often used for readability and simplifying complex queries. While CTEs are most commonly used when running a SELECT query, we can also use CTEs to perform updates with the UPDATE statement. This can be useful when we need to reference the same set of data multiple times or want to update records conditionally.
how to
How to Output Query Results as LaTeX Tables in the DuckDB CLI
This article demonstrates how to output your DuckDB query results in LaTeX table format, which can be useful when preparing academic papers, technical documents, or any content that needs to be typeset using LaTeX.
How to Output Query Results in NDJSON Format in the DuckDB CLI
DuckDB’s CLI allows you to output query results in different formats, including NDJSON (Newline Delimited JSON).
NDJSON is similar to JSON, except that with NDJSON, each line contains its own self-contained JSON document.
This article shows you how to check your current output mode, and then change it to NDJSON.
Concatenating Strings in DuckDB
String concatenation is a common operation when running database queries. It simply involves joining two strings together, end to end. DuckDB provides multiple methods for combining strings, each with its own use cases and advantages.
This article explores the various ways to concatenate strings in DuckDB.
Fix “No function matches the given name and argument types ‘bool_and(INTEGER)'” in DuckDB
If you’re getting an error that reads something like “No function matches the given name and argument types ‘bool_and(INTEGER)’” in DuckDB, it’s probably because you’re passing a non-boolean value to the bool_and() function.
The bool_and() function is for use against boolean expressions, so to fix this issue, be sure that the argument you pass to the function is a boolean expression.
How to Create an In-Memory Database in DuckDB
DuckDB is a high-performance, in-process SQL database management system that supports various modes of operation, including the ability to create an in-memory database. An in-memory database stores all data in RAM, ensuring fast access and excellent performance.
This article explores how to create an in-memory database in DuckDB.
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.
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.
Fix Error “mode should be one of: ascii box column …etc” in DuckDB
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.