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.
Author: Ian
How to Use COALESCE() to Handle NULL Values in DuckDB
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.
What ?TABLE? Means When using .help mode in DuckDB
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.
How to Output Query Results as JSON in the DuckDB CLI
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 reading5 Ways to List All Tables in DuckDB
DuckDB 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 readingUsing CSV Mode in DuckDB CLI
DuckDB’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 readingWhat is NDJSON?
Newline Delimited JSON (NDJSON) is a specialized data format that provides a simple yet powerful way to handle streaming JSON data. While standard JSON is excellent for representing structured data, NDJSON addresses specific use cases where processing large datasets and streaming data is required.
This article takes a quick look at NDJSON and how it differs from regular JSON.
Continue readingWhat is JSON?
JavaScript Object Notation, commonly known as JSON, is a lightweight, text-based data interchange format that has become one of the most widely used standards for transmitting data across the internet.
Despite its origins in JavaScript, JSON has evolved into a language-independent format, making it an essential tool for developers working with any programming language or platform.
Continue readingHow to Change the Output Mode in the DuckDB CLI
DuckDB 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 reading5 Ways to Create a Database in DuckDB
DuckDB 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 reading