Outputting Query Results as a TCL List in the DuckDB CLI

by

This article demonstrates how to output your DuckDB query results in TCL list format, which can be useful when integrating with TCL scripts or systems that expect TCL-formatted data.

Checking the Current Mode

Here are two ways to check your current output mode.

The easiest way to check your current output mode is by using the .mode command:

.mode

Example output:

current output mode: table

My current output mode is table, which formats the results as a table using ASCII characters.

Another way to check the current output mode is by running a sample query to see the output format:

CREATE TABLE products(id INTEGER, name VARCHAR, price DECIMAL);
INSERT INTO products VALUES 
    (1, 'Widget', 19.99), 
    (2, 'Gadget', 24.99);
SELECT * FROM products;

Output:

+----+--------+--------+
| id |  name  | price  |
+----+--------+--------+
| 1  | Widget | 19.990 |
| 2  | Gadget | 24.990 |
+----+--------+--------+

We can see that the results are formatted as an ASCII-art table.

Changing to TCL Mode

To switch to TCL output mode, use:

.mode tcl

Verifying the Change

Check the mode setting:

.mode

This will confirm that HTML mode is active:

current output mode: tcl

The mode is tcl as expected.

Now let’s run a query to see the TCL output:

SELECT * FROM products;

The output will now be formatted as a TCL list:

"id" "name" "price"
"1" "Widget" "19.990"
"2" "Gadget" "24.990"

Each list item is separated by whitepace, including the header.

Switching to Another Mode

To return to the previous output mode or another mode of your choosing:

.mode csv

That changed the output mode to csv, which means that future query results will be output as a comma separated list.

Listing All Available Output Modes

To see all available output modes in DuckDB CLI:

.help mode

Output:

.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 has its own use cases, for example:

  • html for web integration
  • csv for spreadsheet compatibility
  • json for API responses
  • markdown for documentation
  • insert for generating SQL INSERT statements
  • ndjson for streaming data processing

The TCL mode can be particularly useful when:

  • Working with TCL-based systems
  • Generating data for TCL scripts
  • Creating TCL-compatible data exports
  • Testing TCL-based applications
  • Integrating with legacy systems that use TCL