Writing Query Results Back Out to CSV With DuckDB COPY

Reading a CSV into DuckDB is half the job. The other half is getting a result back out in a shape the next tool will accept: a file for a spreadsheet, a feed for a partner, an extract that goes into version control. COPY does it, and its defaults are good. They are also specific, and a few of them, the NULL spelling, the line ending and the missing byte order mark, are the usual reasons for the message "your file does not open correctly". This article covers the statement, the defaults I measured, and the options for changing them. The reading side is in the first query article, and the rules that decide the quoting are explained in how CSV quoting works.

The statement

COPY takes a table or a parenthesized query and writes it to a path. The format is taken from the file extension, and HEADER writes the column names as the first line:

duckdb -c "COPY (SELECT id, name, balance FROM 'customers.csv') TO '/tmp/out.csv' (HEADER, DELIMITER ',')" && cat /tmp/out.csv

id,name,balance
1,Ana Silva,120.5
2,"Smith, Jane",0.0
3,Bo Chen,
4,Cy Dubois,15.75

Header, then four rows. Smith, Jane is quoted because it contains the delimiter, and nothing else is, which is the minimal quoting that RFC 4180 describes. The balance for customer 2 is 0.0 and not 0, since the column was read as DOUBLE; if the downstream tool wants integers where there are integers, cast in the query. Customer 3 has no balance, and the field is empty: 3,Bo Chen,. The result of a SELECT is a perfectly good source, so everything the query language can do, filters, joins, computed columns, goes inside the parentheses.

NULL and the empty string are different

A CSV reader has to decide what an empty field means, and a writer gets to say. DuckDB's defaults keep the two apart:

duckdb -c "COPY (SELECT 1 AS id, NULL AS missing, '' AS empty, 'a,b' AS comma, 'say \"hi\"' AS quote) TO '/dev/stdout' (HEADER)" | cat

id,missing,empty,comma,quote
1,,"","a,b","say ""hi"""

The row is 1,,"","a,b","say ""hi""". The NULL missing column is two commas with nothing between them. The empty string is "", an explicitly empty quoted field. The value containing a comma is quoted, and the value containing double quotes is quoted with each inner quote doubled. The writer keeps the two apart, but do not assume the reader will. I wrote that row to a file and read it back with DuckDB's own read_csv:

duckdb -c "COPY (SELECT 1 AS id, NULL AS missing, '' AS empty) TO '/tmp/rb.csv' (HEADER)" && duckdb -box -c "SELECT id, missing IS NULL AS missing_is_null, empty IS NULL AS empty_is_null FROM read_csv('/tmp/rb.csv')"

┌────┬─────────────────┬───────────────┐
│ id │ missing_is_null │ empty_is_null │
├────┼─────────────────┼───────────────┤
│ 1  │ true            │ true          │
└────┴─────────────────┴───────────────┘

Both missing_is_null and empty_is_null are true: the quoted empty string came back as NULL, so the distinction did not survive the round trip even through the same tool, and most other readers do not draw it either. If the consumer needs one convention, choose it in the query, with coalesce(col, '') or nullif(col, ''), instead of hoping.

When the consumer wants a visible marker for missing values, NULLSTR sets it:

duckdb -c "COPY (SELECT 1 AS id, NULL AS missing, '' AS empty) TO '/dev/stdout' (HEADER, NULLSTR 'NULL')" | cat

id,missing,empty
1,NULL,

1,NULL,. The null is the text NULL. The empty string, interestingly, came out as nothing and not as "". Once NULLSTR is set to something other than the empty string, an empty field no longer means NULL to anyone, so the writer does not need to protect it. I only checked this with a single value, and I would verify it again on the version you run if a downstream parser depends on it.

Quote more than you must

Some importers expect certain columns always quoted, and some expect every text column quoted. FORCE_QUOTE takes a list:

duckdb -c "COPY (SELECT 1 AS id, 'plain' AS name, 2.5 AS amount) TO '/dev/stdout' (HEADER, FORCE_QUOTE (name, amount))" | cat
duckdb -c "COPY (SELECT 1 AS id, 'plain' AS name, DATE '2025-03-14' AS day, TIMESTAMP '2025-03-14 10:15:00' AS ts) TO '/dev/stdout' (HEADER, DATEFORMAT '%d/%m/%Y', TIMESTAMPFORMAT '%d/%m/%Y %H:%M')" | cat

id,"name","amount"
1,"plain","2.5"
id,name,day,ts
1,plain,14/03/2025,14/03/2025 10:15

With FORCE_QUOTE (name, amount), the output is id,"name","amount" for the header and 1,"plain","2.5" for the row. The number was quoted too, and so were the names in the header. That is legal CSV and it changes the reading rules for the consumer: a number inside quotes is text to a strict parser. Use it only when the target needs it.

The second command in the output shows two formatting options. With DATEFORMAT '%d/%m/%Y' and TIMESTAMPFORMAT '%d/%m/%Y %H:%M', the date 2025-03-14 was written as 14/03/2025 and the timestamp as 14/03/2025 10:15. Dates in CSV are among the most expensive ambiguities (mixed formats shows the other end of it), so I keep ISO 8601 unless a recipient forces a different form, and I say in the file name or in a note which one was used.

Another delimiter

DELIMITER changes the separator. A tab produces a TSV file:

duckdb -c "COPY (SELECT id, name FROM 'customers.csv' LIMIT 2) TO '/dev/stdout' (HEADER, DELIMITER E'\t')" | cat -A | head -3

id^Iname$
1^IAna Silva$
2^ISmith, Jane$

cat -A shows the tab as ^I and the end of each line as $. The row 2^ISmith, Jane$ has no quotes: with a tab as the delimiter, a comma is ordinary text. The same applies in reverse for a semicolon file, for which the comma in a decimal number would not need quoting but the semicolon in a text would. The trade-offs among delimiters are in comma, semicolon, tab and pipe.

Compress on the way out

If the path ends in .gz, DuckDB compresses the output with gzip:

duckdb -c "COPY (SELECT * FROM 'customers.csv') TO '/tmp/out.csv.gz' (HEADER)" && file /tmp/out.csv.gz && gunzip -c /tmp/out.csv.gz | head -2

/tmp/out.csv.gz: gzip compressed data, original size modulo 2^32 197
id,name,email,signup_date,balance
1,Ana Silva,[email protected],2025-01-15,120.5

file identifies /tmp/out.csv.gz as gzip compressed data, and gunzip -c shows the header and the first row. DuckDB reads it back with the same file name, so a pipeline of .csv.gz files needs no extra step on either side. For large exports the saving is substantial, and if the extract is going to be queried repeatedly the better output is Parquet, as in the conversion article.

One file per value

PARTITION_BY splits a result into a folder for each distinct value of the chosen columns. I wrote the orders with an order_id up to 70,000, partitioned by country:

cd /batches/csv-0001-0200/work && rm -rf by_country && duckdb -c "COPY (SELECT * FROM 'orders-2m.csv' WHERE order_id <= 70000) TO 'by_country' (FORMAT csv, HEADER, PARTITION_BY (country))" && ls by_country | head -8 && ls by_country/country=FR | head -3 && head -2 by_country/country=FR/*.csv

country=BR
country=DE
country=FR
country=GB
country=IN
country=JP
country=US
data_0.csv
order_id,customer_id,zip,amount,created_at,status,note
12,243905,07969,58.38,2025-04-21 23:17:25,pending,gift

The output folder has country=BR, country=DE, country=FR, country=GB, country=IN, country=JP and country=US, and inside each is a file, data_0.csv. The first rows of the French file start with order_id,customer_id,zip,amount,created_at,status,note: the country column is not in the file, because the folder name carries it. That key=value folder layout is the Hive partitioning convention, and DuckDB can read it back with hive_partitioning. It is useful for sending each region its own file. It is an unpleasant surprise if you expected every file to be complete by itself, so add the column again with a computed name if the recipients need it.

Bytes: line endings and the BOM

Two bytes decide whether a spreadsheet user is happy. I wrote a two row file and a file with an accented name and looked at the bytes:

duckdb -c "COPY (SELECT 1 AS a, 2 AS b) TO '/tmp/le.csv' (HEADER)" && xxd /tmp/le.csv | head -2

00000000: 612c 620a 312c 320a                      a,b.1,2.

duckdb -c "COPY (SELECT 'Zoë' AS name) TO '/tmp/bom.csv' (HEADER)" && xxd /tmp/bom.csv | head -2

00000000: 6e61 6d65 0a5a 6fc3 ab0a                 name.Zo...

The first dump is 61 2c 62 0a 31 2c 32 0a: lines end in 0a, a bare line feed. The second begins 6e 61 6d 65 0a, the word name and a line feed, and then 5a 6f c3 ab, which is Zoë in UTF-8. There is no ef bb bf at the start. So DuckDB's output is LF terminated UTF-8 without a byte order mark. That is correct for almost every tool, and it is what the Excel article says needs a second look: Excel on Windows is widely reported to misread UTF-8 without a BOM, and I could not test Excel here. If your recipients use it, add the three bytes yourself and check them:

duckdb -c "COPY (SELECT 'Zoë' AS name) TO '/tmp/o.csv' (HEADER)" && printf '\xef\xbb\xbf' | cat - /tmp/o.csv > /tmp/o-excel.csv && xxd /tmp/o-excel.csv | head -2

00000000: efbb bf6e 616d 650a 5a6f c3ab 0a         ...name.Zo...

The dump now starts ef bb bf 6e 61 6d 65, the BOM and then name. See the line endings article for the CRLF side.

How to tell it worked

Read the file back with a different tool, not the one that wrote it. Count the lines with wc -l and compare with the row count plus the header. Open it in a hex dump if the file will cross a platform boundary, and check the first bytes and the line endings. Search for a value that contains a comma, a quote and a newline and look at how it was written. And read the file back through DuckDB with SUMMARIZE and compare the null counts to the query you exported.

What the output does not do for you

It does not guess what the recipient expects. It will happily write a million dollar figure as 1000000.0 and a date in whatever format you named, and it will not warn you that the other side wants a comma decimal separator or Windows line endings. It does not guard against spreadsheet formula injection: a text value of =1+1 was written as =1+1 with no quoting or prefix, and a spreadsheet may evaluate it. If your data contains user supplied text and the file will be opened in Excel, prefix such values yourself. And it does not make a file smaller than it needs to be: for anything big and repeated, write Parquet. For a file that people open, the defaults are a good start and the options above close the gaps.