In this article we look at how to automatically enclose values in single quotes when outputting DuckDB query results as a list. Using this method, each value is formatted as SQL literals. This can be useful when you want to escape the values for SQL or for some other purpose.

We also look at how CSV output is sometimes quoted with double quotes, and how we can adjust the results by doing things like removing the headers and changing the list separator.

Setting Up a Sample Table

Let’s create a table with some data that will demonstrate quoted output:

CREATE TABLE movies(id INTEGER, title VARCHAR, director VARCHAR, release_year INTEGER);
INSERT INTO movies VALUES 
    (1, 'The Matrix', 'Lana & Lilly Wachowski', 1999),
    (2, 'Pulp Fiction', 'Quentin Tarantino', 1994),
    (3, 'Schindler''s List', 'Steven Spielberg', 1993);

Some of the data in this table contains spaces and there’s also a single quote in one of the values. We can already see that we need to escape the single quote with another single quote when inserting the data so that it’s not mistaken for the closing quote. Using quote mode in DuckDB will ensure that such data will be escaped when we query the table later.

Switching to Quote Mode

As alluded to, one of the output modes that the DuckDB CLI supports is quote. This outputs the values as a quoted list.

To enable quoted output mode:

.mode quote

We can verify this by running:

.mode

Output:

current output mode: quote

Now let’s query the table:

SELECT * FROM movies;

Output:

'id','title','director','release_year'
1,'The Matrix','Lana & Lilly Wachowski',1999
2,'Pulp Fiction','Quentin Tarantino',1994
3,'Schindler''s List','Steven Spielberg',1993

We can see that each string is surrounded by single quotes. This is also the case for the header.

We can also see that the single quote in the last movie title has been escaped with another single quote (i.e., ' has been replaced with ''). This value is now safe to use with SQL.

Customizing the Separator

By default, the above list was output as a comma separated list. But you can change this if required.

You can change the separator using the .separator command. For example, to use the pipe character (|):

.separator "|"

Now the same query produces:

'id'|'title'|'director'|'release_year'
1|'The Matrix'|'Lana & Lilly Wachowski'|1999
2|'Pulp Fiction'|'Quentin Tarantino'|1994
3|'Schindler''s List'|'Steven Spielberg'|1993

Handling Headers

To remove headers from the output:

.headers off

Now the query shows only the data rows:

1|'The Matrix'|'Lana & Lilly Wachowski'|1999
2|'Pulp Fiction'|'Quentin Tarantino'|1994
3|'Schindler''s List'|'Steven Spielberg'|1993

To restore headers:

.headers on

Quoting with CSV Mode

If you find that quote mode doesn’t quote your results the way you like, you can always try csv mode. This mode will apply double quotes in some cases, and no quotes in other cases (this will depend on the value).

Here’s an example of what I mean:

.headers on
.mode csv
SELECT * FROM movies;

Output:

id,title,director,release_year
1,The Matrix,Lana & Lilly Wachowski,1999
2,Pulp Fiction,Quentin Tarantino,1994
3,"Schindler's List",Steven Spielberg,1993

In this case, most values are not quoted. The only value that was quoted is the one that contains the single quote. Also, this value is escaped with double quotes as opposed to single quotes like when using quote mode.

Lists with No Quotes

Using list mode will output the above data without any quotes:

.mode list
SELECT * FROM movies;

Output:

id|title|director|release_year
1|The Matrix|Lana & Lilly Wachowski|1999
2|Pulp Fiction|Quentin Tarantino|1994
3|Schindler's List|Steven Spielberg|1993

If you prefer to have it separated by a comma as opposed to the pipe character, you can use the .separator command to change the separator:

.separator ", "
SELECT * FROM movies;

Output:

id, title, director, release_year
1, The Matrix, Lana & Lilly Wachowski, 1999
2, Pulp Fiction, Quentin Tarantino, 1994
3, Schindler's List, Steven Spielberg, 1993

In this case I added a space after the comma, but you can choose whether or not to do this.