If you’ve ever run .help mode in the DuckDB command line interface (CLI), you may have noticed that it returns information about the .mode command, including its syntax.

You might’ve noticed that there’s a ?TABLE? part in the syntax. What’s this about? Maybe you’ve tried it out and found that it didn’t appear to do anything?

In this article, I’ll explain what the ?TABLE? part is for, and I’ll run an example to demonstrate.

Running .help mode

Running .help mode in the DuckDB CLI returns the syntax of the .mode command, followed by the valid output options.

Let’s run it:

.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

The first line shows us the syntax for the .mode command. It looks like this:

.mode MODE ?TABLE?       Set output mode

Based on this information, we might assume that we can append any mode with a table name. But this isn’t quite true.

While we can certainly append any mode with a table name, in most cases it has no effect on the output.

The ?TABLE? Option Applies to the insert Option

The ?TABLE? part can be used when setting the output to insert.

?TABLE? is a placeholder for a user defined table name. So if we switch to insert mode, we have the option of specifying a table name for the INSERT statements.

Without ?TABLE?

Here’s an example of using the insert mode without the ?TABLE? option:

.mode insert
SELECT * FROM users;

Output:

INSERT INTO "table"(id,"name",city) VALUES(1,'Azur','New York');
INSERT INTO "table"(id,"name",city) VALUES(2,'Beeble','San Francisco');
INSERT INTO "table"(id,"name",city) VALUES(3,'Chantalope','Chicago');

By default, all INSERT statements insert the data into a table called "table".

With ?TABLE?

Now here’s an example of using the insert mode with the ?TABLE? option:

.mode insert players
SELECT * FROM users;

Output:

INSERT INTO players(id,"name",city) VALUES(1,'Azur','New York');
INSERT INTO players(id,"name",city) VALUES(2,'Beeble','San Francisco');
INSERT INTO players(id,"name",city) VALUES(3,'Chantalope','Chicago');

This time all INSERT statements specify the players table, so the assumption is that the destination table is called players.

As mentioned, using the ?TABLE? option with other output options doesn’t do anything (at least, not at the time of this writing). Perhaps a future release will make use of ?TABLE? for other output options, but for now it appears to only work on the INSERT option.

The SQLite Equivalent

SQLite’s .help command provides a bit more information about this. When we run .help mode in SQLite, it explicitly states that the TABLE option is only for the HTML mode:

.help mode

Output in SQLite:

.mode MODE ?OPTIONS?     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)
     html        HTML <table> code
     insert      SQL insert statements for TABLE
     json        Results in a JSON array
     line        One value per line
     list        Values delimited by "|"
     markdown    Markdown table format
     qbox        Shorthand for "box --wrap 60 --quote"
     quote       Escape answers as for SQL
     table       ASCII-art table
     tabs        Tab-separated values
     tcl         TCL list elements
   OPTIONS: (for columnar modes or insert mode):
     --wrap N       Wrap output lines to no longer than N characters
     --wordwrap B   Wrap or not at word boundaries per B (on/off)
     --ww           Shorthand for "--wordwrap 1"
     --quote        Quote output text as SQL literals
     --noquote      Do not quote output text
     TABLE          The name of SQL table used for "insert" mode

The TABLE option is one of several options that we can specify, and SQLite states that it’s for The name of SQL table used for "insert" mode. See Format SQLite Query Results as an HTML Table for an example of doing this in SQLite.