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.

Examining Your Current Mode

First, let’s check the current output mode. Here are two ways to do this:

Using the .mode command:

.mode

Sample output:

current output mode: column

My current output mode is column.

We can also run a sample query to see the output format:

CREATE TABLE pets(id INTEGER, name VARCHAR, species VARCHAR, age INTEGER);
INSERT INTO pets VALUES 
    (1, 'Whiskers', 'Cat', 4),
    (2, 'Rover', 'Dog', 6);
SELECT * FROM pets;

Output:

id  name      species  age
--  --------  -------  ---
1   Whiskers  Cat      4  
2   Rover     Dog      6  

The result of the query is returned using the column format.

Switching to List Mode

To enable pipe-separated list output mode, enter:

.mode list

Confirming the Change

Verify the mode change:

.mode

Output:

current output mode: list

It indicates that list mode is the current output mode.

Let’s now execute a query to see the list-formatted output:

SELECT * FROM pets;

The results appear as pipe-separated values:

id|name|species|age
1|Whiskers|Cat|4
2|Rover|Dog|6

Notice that list mode:

• Uses the pipe character (|) as a field separator
• Presents each record on a single line
• Includes column headers by default
• Doesn’t include any additional formatting characters
• Produces compact, easily parseable output

Removing the Header

If you prefer to output just the data without column headers, you can turn them off using:

.headers off

Now when you run a query:

SELECT * FROM pets;

The output will show only the data rows:

1|Whiskers|Cat|4
2|Rover|Dog|6

To turn headers back on:

.headers on

Returning to the Previous Mode

To switch back to the previous format:

.mode column

Replace column with whatever format you need. It doesn’t have to be the previous mode, and you can always keep it in list mode if required. All future queries will be output in this mode until you change the output mode.

Available Output Modes

To discover all supported output modes:

.help mode

This reveals the complete set of available modes:

.mode MODE ?TABLE?       Set output mode
   MODE is one of:
     ascii     Columns/rows delimited by 0x1F and 0x1E
     box       Tables using unicode box-drawing characters
     csv       Comma-separated values
     column    Output in columns.  (See .width)
     duckbox   Tables with extensive features
     html      HTML <table> code
     insert    SQL insert statements for TABLE
     json      Results in a JSON array
     jsonlines Results in a NDJSON
     latex     LaTeX tabular environment code
     line      One value per line
     list      Values delimited by "|"
     markdown  Markdown table format
     quote     Escape answers as for SQL
     table     ASCII-art table
     tabs      Tab-separated values
     tcl       TCL list elements
     trash     No output

Each mode serves different purposes, for example:

• csv for comma-separated values
• json for structured data exchange
• markdown for documentation
• html for web content
• ndjson for streaming data

List mode proves especially valuable when:

• Processing data with Unix tools like awk or sed
• Importing data into systems that expect pipe-delimited input
• Creating compact, parseable output
• Generating input for ETL processes
• Working with command-line data processing pipelines
• Preparing data for bulk imports