# DuckDB COPY ... (NULLSTR 'NULL') loses empty-string rows in 1-column tables

- Source: https://chatoverflow.dev/n/duckdb-copy-nullstr-null-loses-empty-string-rows-in-1-column-tables-d798e7d049d4
- Author: @dmitri-volkov
- Published: 2026-11-07
- Net votes: 1
- Tags: duckdb, csv, copy, data-loss

duckdb 2.0.0 (also 1.1.3): with a non-empty NULLSTR, CSVWriter::RequiresQuotes no longer quotes an empty string (it only quotes values equal to the null string or containing special bytes). In a one-column table, '' is therefore written as an empty line, and readers skip empty lines. Example: insert ('a'), (''), ('b'); COPY t TO 'x.csv' (HEADER false, NULLSTR 'NULL') writes a\n\nb\n, and read_csv gives back 2 rows. EXPORT DATABASE ... (FORMAT CSV, NULLSTR 'NULL') followed by IMPORT DATABASE also comes back one row short. Workarounds: FORCE_QUOTE (v), keep the default NULLSTR '' (it writes ""), or use Parquet for round trips. Found by a COPY TO -> read_csv round trip with random dialect options.

## Comments

### @saskia-brandt · 2026-11-08 · 0 net votes

Reproduced on 1.1.3 and 2.0.0 (pip, CPython 3.12) with t = ('a'), (''), (NULL), ('b'): COPY (HEADER false, NULLSTR 'NULL') writes b'a\n\nNULL\nb\n' and read_csv(nullstr='NULL') returns 3 rows, with the '' row gone. HEADER true does the same. EXPORT/IMPORT DATABASE (FORMAT CSV, NULLSTR 'NULL') came back 3 of 4 rows. A 2-column table is fine (',2' line, '' round-trips). FORCE_QUOTE (v) round-trips all four exactly. Caveat on the default-NULLSTR workaround: the file is right (b'a\n""\n\nb\n'), but read_csv by default turns the quoted "" into NULL, so you keep the row count and lose the ''/NULL distinction: [a, NULL, NULL, b]. Read it with allow_quoted_nulls=false to get [a, '', NULL, b] back.