Output Query Results as a Tab-Separated List in DuckDB

DuckDB’s command-line interface (CLI) provides a simple built-in method for outputting query results as tab-separated values (TSV) using the .mode tabs command.

This article takes a quick look at this output mode and explores some of the options available for when outputting query results as a tab-separated list.

Basic Usage

The simplest way to output results as TSV is:

-- Set output mode to tabs
.mode tabs

-- Run your query
SELECT * FROM your_table;

This is done from within the DuckDB CLI client. The .mode command is known as a “dot command”. These are commands that we can use to configure the CLI environment.

Once you use the .mode command, all subsequent queries will be output using that mode (unless of course you change to another mode later).

Example

Here’s an example to demonstrate:

-- Configure TSV output
.mode tabs

-- Create a sample table
CREATE TABLE employees (
    id INTEGER,
    name VARCHAR,
    department VARCHAR,
    salary DECIMAL(10,2),
    hire_date DATE
);

-- Insert sample data
INSERT INTO employees VALUES
    (1, 'John Smith', 'Engineering', 85000.00, '2022-03-15'),
    (2, 'Maria Garcia', 'Marketing', 75000.00, '2021-06-01'),
    (3, 'James Brown', 'Engineering', 92000.00, '2023-01-10'),
    (4, 'Sarah Johnson', 'HR', 65000.00, '2022-09-20'),
    (5, NULL, 'Engineering', 78000.00, '2023-04-01');

-- Run a query with the results in TSV format
SELECT 
    id,
    name,
    department,
    salary,
    hire_date
FROM employees
WHERE salary > 70000
ORDER BY salary DESC;

Output:

id	name	department	salary	hire_date
3	James Brown	Engineering	92000.00	2023-01-10
1	John Smith	Engineering	85000.00	2022-03-15
5		Engineering	78000.00	2023-04-01
2	Maria Garcia	Marketing	75000.00	2021-06-01

The values are separated by a tab character. Given each value is a different length, this can result in the values not lining up like they would if we were using another mode, such as column. But if we need it to be tab-separated, then we’re not necessarily looking for alignment or other formatting; we’re more interested in the values being separated by a tab.

We can also see that employee number 5 has no name (it’s a NULL value). We can deal with that if required (more on this later).

Configuring Headers

You can control whether column headers appear in the output:

-- Include headers (default)
.headers on

-- Or exclude headers if needed
.headers off

NULL Value Display

By default, NULL values appear as empty fields in the TSV output. You can customize how NULL values are displayed:

-- Set custom NULL display
.nullvalue "NULL"

-- Or use empty string (default)
.nullvalue ""

Complete Example

Let’s run the query again, but with headers off, and a string for NULL values:

-- Configure TSV output
.mode tabs
.headers off
.nullvalue "NULL"

-- Run a query with the results in TSV format
SELECT 
    id,
    name,
    department,
    salary,
    hire_date
FROM employees
WHERE salary > 70000
ORDER BY salary DESC;

Output:

3	James Brown	Engineering	92000.00	2023-01-10
1	John Smith	Engineering	85000.00	2022-03-15
5	NULL	Engineering	78000.00	2023-04-01
2	Maria Garcia	Marketing	75000.00	2021-06-01

This time we don’t get a header row, and we get NULL in place of the NULL value.

Resetting to Default Settings

If you need to return to the default settings:

.mode duckbox
.headers on
.nullvalue ""