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.

Enabling line Mode

In the DuckDB CLI, line mode formats query results with one value per line. Each column is presented kind of as a row of name/value pairs, as opposed to columns like when it’s in duckbox or table format. This is sometimes referred to as “vertical query output” or simply “vertical output”.

To enable line mode in the DuckDB CLI:

.mode line

Once enabled, all query subsequent results will be automatically formatted vertically until the mode is changed.

Example

After enabling line mode, the results of all subsequent queries are output vertically.

Here’s an example to demonstrate:

SELECT * FROM products;

Output example:

   ProductId = 1
    VendorId = 1001
 ProductName = Left handed screwdriver
ProductPrice = 25.99

   ProductId = 2
    VendorId = 1001
 ProductName = Right handed screwdriver
ProductPrice = 25.99

   ProductId = 3
    VendorId = 1001
 ProductName = Long Weight (blue)
ProductPrice = 14.75

We can see that the columns are presented along the left, and the values are on the right, with an equals sign in between each column and its value.

This can be handy if the query returns a lot of columns and you don’t want to scroll sideways to view all values in a row. It can also be handy if one or two columns contain a long value to the extent where expanding the width of the column pushes all other columns off to the side, forcing you to scroll sideways (or forcing the rows to wrap to the next row).

Changing Back to a Horizontal Mode

As mentioned, vertical query output will remain in place until you switch to another mode. To do this, simply use .mode followed by the desired mode. For example, to switch to table output, use the following:

.mode table

Now, query results will use that format:

SELECT * FROM products;

Output:

+-----------+----------+---------------------------------+--------------+
| ProductId | VendorId |           ProductName           | ProductPrice |
+-----------+----------+---------------------------------+--------------+
| 1         | 1001     | Left handed screwdriver         | 25.99        |
| 2         | 1001     | Right handed screwdriver        | 25.99        |
| 3         | 1001     | Long Weight (blue)              | 14.75        |
+-----------+----------+---------------------------------+--------------+

Enabling Vertical Output When Launching DuckDB

You can save time by passing the -line argument when launching DuckDB. This removes the need to use the .mode command to switch to vertical query output.

Example:

duckdb test.duckdb -line

That launches DuckDB in the test.duckdb database and enables line mode.

You still have the option of using the .mode command to switch to another mode if needed.