This tutorial walks you through outputting DuckDB query results in list format, which presents each record as a pipe-separated list of values. This format is handy for data processing tasks and when working with tools that expect pipe-delimited input.
Continue readingTag: create query
Suppressing Query Output in the DuckDB CLI
DuckDB’s command-line interface (CLI) provides a way to suppress query results using the .mode trash
command. This feature is particularly useful in scenarios where you need to execute queries but don’t want their results to be displayed.
How to Output Query Results as HTML Tables in the DuckDB CLI
The DuckDB command line interface (CLI) provides us with the ability to output query results in various formats. One of these formatting options is HTML.
This article shows you how to output your DuckDB query results as HTML tables, which can be useful when you need to include the results in web pages or documentation.
Continue readingFix “Sample method … cannot be used with a discrete sample count” When Using the SAMPLE Clause in DuckDB
If you’re getting an error that reads something like “Sample method System cannot be used with a discrete sample count” when using the SAMPLE
clause in DuckDB, it looks like you’re specifying an invalid sampling method for the context with which you’re using the SAMPLE
clause. Perhaps you’re using system or bernoulli, when you should be using reservoir.
Adding Quotes Around Field Values When Outputting DuckDB Query Results as a List
In this article we look at how to automatically enclose values in single quotes when outputting DuckDB query results as a list. Using this method, each value is formatted as SQL literals. This can be useful when you want to escape the values for SQL or for some other purpose.
We also look at how CSV output is sometimes quoted with double quotes, and how we can adjust the results by doing things like removing the headers and changing the list separator.
Continue readingSampling Rows from a Table in DuckDB with the SAMPLE Clause
DuckDB’s SAMPLE
clause is a handy feature that allows us to work with a random subset of our data. This is particularly useful when dealing with large datasets where processing the entire dataset might be time-consuming or unnecessary for exploratory data analysis, testing queries, or creating representative samples.
When we use this clause, we can specify the absolute number of rows to return, or a percentage of rows. We also have an option of sampling method to use.
Continue readingDuckDB Allows Queries to Begin with ‘FROM…’
If you find yourself running a lot of ad-hoc queries that start with SELECT * FROM
, you might be interested in this interesting feature of DuckDB.
DuckDB allows us to start queries with the FROM
clause. When we do this, DuckDB will return all columns without us needing to specify SELECT *
.
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.
Continue readingHow 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.
Continue readingEnable 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.