Exporting Query Results to CSV From Five Major Databases

Every database can write a query result to a CSV file, and every one does it a little differently. Some write on your machine and some on the server. Some quote and some escape. One ends rows with a carriage return and a line feed and the rest do not, and one cannot produce standards-compliant CSV with the recipe on the first page of search results. This guide runs the same awkward data through five engines, reads each file back with a real CSV parser to see what survived, and collects the differences that decide whether the file works for whoever reads it next. The articles linked from each section go deep on one engine.

What an export has to get right

A CSV export is a set of decisions the database makes for you unless you make them yourself. Seven of them decide whether the file is usable:

1. Quoting. A field with a comma, a quote or a newline must be quoted, and a quote inside a field doubled. RFC 4180 is the closest thing to a specification, and not every tool follows it.

2. NULL and the empty string. CSV has no null. A tool decides whether a missing value and an empty string are written the same way.

3. Types become text. Booleans, dates, timestamps and numbers each get a spelling, and the spelling may depend on a session setting.

4. The header row. Present, absent or optional.

5. Line endings and encoding. LF or CRLF; UTF-8 or something else.

6. Where the file is written. On the machine you are sitting at, or on the database server.

7. What happens when it fails. A partial file, an empty file, an exit status.

The rest of this guide goes through the five engines against that list.

The test data

One table, ex_people, with eight rows chosen to break each of those:

The columns also include a boolean, a decimal and a timestamp. The same rows were created in PostgreSQL, MySQL, SQLite and DuckDB with the closest native types, and as documents in MongoDB. After each export I read the file with Python's csv module and compared the name and note columns with what the table held. A file "reads back exactly" when all eight pairs match, including the newline and the spaces.

PostgreSQL

The tool is psql, and it has two ways to write CSV. \copy writes a file on your machine:

psql -c "\copy (SELECT * FROM ex_people ORDER BY id) TO STDOUT WITH (FORMAT csv, HEADER)"

id,name,note,active,score,joined
1,Ana Silva,plain,t,12.50,2025-03-01 09:30:00+00
2,"O'Neil, Pat","has, a comma",f,0.00,2025-03-02 23:59:59+00
3,"Bo ""Bee"" Chen","says ""hi""",t,7.00,2025-03-03 00:00:00+00
4,Cy,"line one
line two",t,,2025-03-04 12:00:00+00
5,Di,"",f,3.14,2025-03-05 08:15:30+00
6,Ed,,,100.00,
7,Zoë Ünal,ünïcode ✓ ñ,t,1234.50,2025-03-07 17:45:00+00
8,Fay, padded ,t,-5.25,2025-03-08 01:02:03+00

The output is what you would write by hand. "O'Neil, Pat" and "Bo ""Bee"" Chen" are quoted correctly, the newline in row 4 sits inside quotes, row 5's empty string is written "" and row 6's NULL as nothing, and the boolean is t or f. The timestamp ends in +00: it is a timestamptz, printed in the session's time zone. psql --csv is the quick way to see a result:

psql --csv -c "SELECT * FROM ex_people ORDER BY id"

id,name,note,active,score,joined
1,Ana Silva,plain,t,12.50,2025-03-01 09:30:00+00
2,"O'Neil, Pat","has, a comma",f,0.00,2025-03-02 23:59:59+00
3,"Bo ""Bee"" Chen","says ""hi""",t,7.00,2025-03-03 00:00:00+00
4,Cy,"line one
line two",t,,2025-03-04 12:00:00+00
5,Di,,f,3.14,2025-03-05 08:15:30+00
6,Ed,,,100.00,
7,Zoë Ünal,ünïcode ✓ ñ,t,1234.50,2025-03-07 17:45:00+00
8,Fay, padded ,t,-5.25,2025-03-08 01:02:03+00

It differs in one place. Row 5 is 5,Di,,f,3.14 here, where \copy wrote 5,Di,"",f,3.14. psql --csv prints the empty string and the NULL the same way. Both read back 8 of 8. The full story, with options, encodings, the permission error for server-side COPY and the variables that do not interpolate, is in exporting a PostgreSQL query with psql. Loading the other way is in loading a CSV into PostgreSQL.

MySQL

MySQL offers SELECT ... INTO OUTFILE, which runs on the server, and the mysql client, which can write tab-separated text. The recipe that turns up first for "MySQL to CSV" is this:

mysql -h mysql -uroot -psecret lab --default-character-set=utf8mb4 -e "SELECT * FROM ex_people ORDER BY id INTO OUTFILE '/var/lib/mysql-files/hub_ex.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\"' LINES TERMINATED BY '\n'" 2>/dev/null
mysql -h mysql -uroot -psecret lab --default-character-set=utf8mb4 -N -B -r -e "SELECT LOAD_FILE('/var/lib/mysql-files/hub_ex.csv')" 2>/dev/null

1,"Ana Silva","plain",1,12.50,"2025-03-01 09:30:00"
2,"O'Neil, Pat","has, a comma",0,0.00,"2025-03-02 23:59:59"
3,"Bo ""Bee"" Chen","says ""hi""",1,7.00,"2025-03-03 00:00:00"
4,"Cy","line one"
line two",1,"N,"2025-03-04 12:00:00"
5,"Di","",0,3.14,"2025-03-05 08:15:30"
6,"Ed","N,"N,100.00,"N
7,"Zoë Ünal","ünïcode ✓ ñ",1,1234.50,"2025-03-07 17:45:00"
8,"Fay"," padded ",1,-5.25,"2025-03-08 01:02:03"

Look at row 4. The newline in the note ended the quoted field, and the NULL score was written as "N: the ESCAPED BY '"' clause makes the quote the escape character for everything. Rows 6 and the rest are shifted. Read back, only 4 of 8 pairs matched. With MySQL's default escaping, 5 of 8 matched, because it writes \N for NULL and backslash-quote for a quote, which are MySQL's conventions and not CSV's. With ESCAPED BY '' and the quotes doubled by hand in the query using REPLACE, 7 of 8 matched; the eighth is a NULL written as the word NULL.

The client route is the plain one:

mysql -h mysql -uroot -psecret lab --default-character-set=utf8mb4 --batch -e "SELECT * FROM ex_people ORDER BY id" 2>/dev/null

id	name	note	active	score	joined
1	Ana Silva	plain	1	12.50	2025-03-01 09:30:00
2	O'Neil, Pat	has, a comma	0	0.00	2025-03-02 23:59:59
3	Bo "Bee" Chen	says "hi"	1	7.00	2025-03-03 00:00:00
4	Cy	line one\nline two	1	NULL	2025-03-04 12:00:00
5	Di		0	3.14	2025-03-05 08:15:30
6	Ed	NULL	NULL	100.00	NULL
7	Zoë Ünal	ünïcode ✓ ñ	1	1234.50	2025-03-07 17:45:00
8	Fay	 padded 	1	-5.25	2025-03-08 01:02:03

It is tab-separated, writes the newline as \n, NULL as the word NULL and booleans as 1 and 0. It read back correctly in 6 of 8 pairs. A short Python script with a streaming cursor and the csv module reads back 8 of 8 and avoids the file-on-the-server problem. All of this, including the secure_file_priv error, the header trick and the backslash trap, is in exporting MySQL query results with INTO OUTFILE. To load the file back, see importing a CSV into MySQL.

SQLite

sqlite3 -csv -header work/export/ex.sqlite "SELECT * FROM ex_people ORDER BY id"

id,name,note,active,score,joined
1,"Ana Silva",plain,1,12.5,"2025-03-01 09:30:00"
2,"O'Neil, Pat","has, a comma",0,0.0,"2025-03-02 23:59:59"
3,"Bo ""Bee"" Chen","says ""hi""",1,7.0,"2025-03-03 00:00:00"
4,Cy,"line one
line two",1,,"2025-03-04 12:00:00"
5,Di,"",0,3.14,"2025-03-05 08:15:30"
6,Ed,,,100.0,
7,"Zoë Ünal","ünïcode ✓ ñ",1,1234.5,"2025-03-07 17:45:00"
8,Fay," padded ",1,-5.25,"2025-03-08 01:02:03"

sqlite3 -csv -header reads back 8 of 8. Note what is different: it quotes more than needed ("Ana Silva", "2025-03-01 09:30:00"), it keeps NULL and the empty string apart ("" and nothing), a REAL column prints as stored (12.5, not 12.50) and the boolean is the integer 1 or 0. The line endings are not visible, but they are the thing to know: SQLite ends every row with CRLF.

printf '%-9s %6s %6s\n' file CR LF
for f in pg_csv pg_copy sqlite duckdb mongo; do printf '%-9s %6d %6d\n' $f $(tr -cd '\r' < work/export/$f.csv | wc -c) $(tr -cd '\n' < work/export/$f.csv | wc -c); done

file          CR     LF
pg_csv         0     10
pg_copy        0     10
sqlite         9     10
duckdb         0     10
mongo          0     10

The count of carriage returns (CR) and line feeds (LF) in each file: sqlite has 9 CRs, one per row including the header, and 10 LFs, those 9 plus the newline inside row 4's note. The other four files have no CRs. Exporting a CSV with headers from SQLite shows how to switch to LF and what happens to a blob. The older SQLite export article covers the dot-command route.

DuckDB

duckdb work/export/ex.duckdb -c "COPY (SELECT * FROM ex_people ORDER BY id) TO '/dev/stdout' (FORMAT csv, HEADER)"

id,name,note,active,score,joined
1,Ana Silva,plain,true,12.50,2025-03-01 09:30:00+00
2,"O'Neil, Pat","has, a comma",false,0.00,2025-03-02 23:59:59+00
3,"Bo ""Bee"" Chen","says ""hi""",true,7.00,2025-03-03 00:00:00+00
4,Cy,"line one
line two",true,,2025-03-04 12:00:00+00
5,Di,"",false,3.14,2025-03-05 08:15:30+00
6,Ed,,,100.00,
7,Zoë Ünal,ünïcode ✓ ñ,true,1234.50,2025-03-07 17:45:00+00
8,Fay, padded ,true,-5.25,2025-03-08 01:02:03+00

DuckDB's COPY (query) TO file (FORMAT csv, HEADER) read back 8 of 8. The boolean is true or false, the timestamp keeps its +00 offset, an empty string is "" and a NULL is empty:

duckdb work/export/ex.duckdb -c "COPY (SELECT id, note, active, joined FROM ex_people WHERE id IN (4, 5, 6) ORDER BY id) TO '/dev/stdout' (FORMAT csv, HEADER)"

id,note,active,joined
4,"line one
line two",true,2025-03-04 12:00:00+00
5,"",false,2025-03-05 08:15:30+00
6,,,

DuckDB can also read the other engines' files and write the result back out, which makes it a good last step for tidying an export: writing query results back out describes it.

MongoDB

mongoexport --db lab --collection ex_people --type=csv --fields _id,name,note,active,score,joined --sort '{"_id": 1}' 2>/dev/null

_id,name,note,active,score,joined
1,Ana Silva,plain,true,12.50,2025-03-01T09:30:00.000Z
2,"O'Neil, Pat","has, a comma",false,0,2025-03-02T23:59:59.000Z
3,"Bo ""Bee"" Chen","says ""hi""",true,7,2025-03-03T00:00:00.000Z
4,Cy,"line one
line two",true,,2025-03-04T12:00:00.000Z
5,Di,,false,3.14,2025-03-05T08:15:30.000Z
6,Ed,,,100,
7,Zoë Ünal,ünïcode ✓ ñ,true,1234.50,2025-03-07T17:45:00.000Z
8,Fay," padded ",true,-5.25,2025-03-08T01:02:03.000Z

mongoexport --type=csv needs a field list, and with one it read back 8 of 8. Booleans are true and false, a Decimal128 keeps its digits (12.50, but 0 and 7) and the date is ISO 8601 in UTC with milliseconds. Documents 5 and 6 differ in the source (an empty string and a null) and not in the file. Arrays, nested documents, ObjectIds and a typo in the collection name each behave in a way that needs a paragraph; they have one in exporting a MongoDB collection with mongoexport.

The scoreboard

Every file read back with the same script, comparing the name and note of each row:

for f in pg_csv pg_copy sqlite duckdb mongo mysql_v1 mysql_v2 mysql_v3; do printf '%-10s ' $f; python lab/export/roundtrip.py work/export/$f.csv | head -n 2 | tr '\n' ' ' | cut -c1-100; done
printf '%-10s ' mysql_batch; python lab/export/roundtrip.py work/export/mysql_batch.tsv "$(printf '\t')" | head -n 2 | tr '\n' ' ' | cut -c1-100

pg_csv     8 records, field counts [6] 8 of 8 name/note pairs read back exactly 
pg_copy    8 records, field counts [6] 8 of 8 name/note pairs read back exactly 
sqlite     8 records, field counts [6] 8 of 8 name/note pairs read back exactly 
duckdb     8 records, field counts [6] 8 of 8 name/note pairs read back exactly 
mongo      8 records, field counts [8] 8 of 8 name/note pairs read back exactly 
mysql_v1   8 records, field counts [3, 6, 9] 4 of 8 name/note pairs read back exactly 
mysql_v2   8 records, field counts [6] 5 of 8 name/note pairs read back exactly 
mysql_v3   8 records, field counts [6] 7 of 8 name/note pairs read back exactly 
mysql_batch 8 records, field counts [6] 6 of 8 name/note pairs read back exactly 

Eight of eight for PostgreSQL (both routes), SQLite, DuckDB and MongoDB. MySQL's three INTO OUTFILE variants returned 4, 5 and 7, with record widths of 3, 6 and 9 for the first, which is how a corrupted file shows itself. mysql_batch was parsed as tab-separated and returned 6. Treat this as a reading of these tools on this data and these versions, not a verdict on the engines: MySQL's INTO OUTFILE is built to feed LOAD DATA, where its conventions are correct.

The differences in one table

Everything below was observed in the outputs above, except where a note says otherwise.

Two columns of that table matter most for compatibility. NULL versus the empty string decides whether the consumer can tell a missing value from a blank one; if it matters, use \copy, SQLite or DuckDB, or write an explicit marker. Row ending decides whether a Unix pipeline works on the output without a cleanup.

Where the file lands

There are two kinds of export, and the difference is operational. A client-side export (\copy, sqlite3, DuckDB, mongoexport, a script) writes where you run the tool, with your permissions. A server-side export (COPY ... TO 'file', INTO OUTFILE) writes on the database host, as the server's user, in a directory the server allows, and needs a privilege most roles do not have. The two permission errors I met were PostgreSQL's permission denied to COPY to a file and MySQL's ERROR 1290 ... --secure-file-priv, and a third, ERROR 1086 ... already exists, for a file that was already there. On a managed database service, server-side writing is usually unavailable. Prefer client-side.

When the export fails

A tool that fails halfway can leave a file behind, and each of these tools did something different:

• psql with \copy to a file: exit status 1 and a 9 byte file holding only the header.

• sqlite3 against a missing table: exit status 1 and a 0 byte file.

• mongoexport with a misspelled collection: exit status 0, exported 0 records and a 0 byte file. With --assertExists it failed properly.

• A shell pipeline such as psql ... | gzip: the pipeline reported the status of gzip, 0, after psql had failed, unless pipefail was set.

A consumer that checks only that a file exists will take any of them. The rule that fixes all four: write to a temporary name, check, then rename. The nightly export article shows a script that does it, with a lock, a row count check and a manifest, and the gzip article covers compressing in the same pipe. The truncated-file case, where the file is complete but short, has its own checks in testing a CSV export for truncated last lines.

Habits that apply to every engine

• Format the awkward types in the query. Dates in ISO 8601 with a zone you chose, booleans as true and false, numbers with a fixed number of decimals. Do not rely on session settings, which change the output between machines; writing dates and booleans others can read shows the same instant exported three ways.

• Test with an awkward table. Eight rows like the ones above, exported and read back, cost five minutes and catch the failures that real data would show you months later.

• Count. Compare the number of records in the file with a count(*) from the same moment, with a CSV parser and not wc -l, as in checking row and column counts.

• Write a manifest. A size, a row count and a SHA-256 next to each file make the next hop's validation possible; the contract article lists what else to agree.

• Let the database write the text. On a 2,000,000 row table, \copy used 11 MB of memory and 3.2 s, fetchall() used 1.5 GB, and pandas used 2.0 GB and changed 103.10 to 103.1. Exporting large tables has all the numbers.

• Validate on the receiving side. The validation guide is the other half of this one.

Reading it back in: does NULL survive?

An export is only half of a round trip. If the file will be loaded into a database again, the question is whether a missing value comes back missing and a blank one comes back blank. I loaded the PostgreSQL files back into PostgreSQL, and the other engines' files into their own engines:

psql -q <<'SQL'
DROP TABLE IF EXISTS back_a, back_b;
CREATE TABLE back_a (id int, name text, note text, active boolean, score numeric(8,2), joined timestamptz);
CREATE TABLE back_b (LIKE back_a);
\copy back_a FROM 'work/export/pg_copy.csv' WITH (FORMAT csv, HEADER)
\copy back_b FROM 'work/export/pg_csv.csv' WITH (FORMAT csv, HEADER)
SELECT 'from \copy export' AS file, count(*) FILTER (WHERE note IS NULL) AS note_null, count(*) FILTER (WHERE note = '') AS note_empty FROM back_a
UNION ALL
SELECT 'from psql --csv export', count(*) FILTER (WHERE note IS NULL), count(*) FILTER (WHERE note = '') FROM back_b;
SQL

          file          | note_null | note_empty 
------------------------+-----------+------------
 from \copy export      |         1 |          1
 from psql --csv export |         2 |          0
(2 rows)

The \copy export, loaded with COPY FROM, came back with 1 NULL note and 1 empty note, the original counts. The psql --csv export came back with 2 NULLs and no empty notes: it wrote both the same way, and a loader reads an unquoted empty field as NULL. The difference between the two routes in the export is the difference in the data you get back.

echo "DuckDB reading its own export:"
duckdb -box -c "SELECT count(*) FILTER (WHERE note IS NULL) AS note_null, count(*) FILTER (WHERE note = '') AS note_empty FROM read_csv('work/export/duckdb.csv')"
echo "SQLite .import of its own export:"
rm -f work/export/back.sqlite; sqlite3 work/export/back.sqlite ".mode csv" ".import work/export/sqlite.csv t"  "SELECT sum(note IS NULL) AS note_null, sum(note = '') AS note_empty FROM t"
echo "pandas on the PG \copy export:"
python -c "
import pandas as pd
df = pd.read_csv('work/export/pg_copy.csv')
print('note missing:', int(df.note.isna().sum()), ' (the empty string and the NULL are both NaN)')
df = pd.read_csv('work/export/pg_copy.csv', keep_default_na=False)
print('with keep_default_na=False: empty strings', int((df.note == '').sum()), ' (the NULL is empty too)')
"

DuckDB reading its own export:
┌───────────┬────────────┐
│ note_null │ note_empty │
├───────────┼────────────┤
│ 2         │ 0          │
└───────────┴────────────┘
SQLite .import of its own export:
0,2
pandas on the PG \copy export:
note missing: 2  (the empty string and the NULL are both NaN)
with keep_default_na=False: empty strings 2  (the NULL is empty too)

DuckDB reading its own export also returned 2 NULLs and 0 empty notes, even though the file has "" for one of them: by default DuckDB reads an empty field as NULL, quoted or not. SQLite's .import did the opposite and returned 0 NULLs and 2 empty strings, since it imports empty fields as empty text. pandas reads both as NaN unless you turn off its NA handling, and with keep_default_na=False it reads both as empty strings. Four readers, four answers for the same file. If the distinction matters, do not depend on the quoting: write an explicit marker (NULL, \N, <missing>), tell the reader about it and test the round trip once.

How common readers see these files

The same five files, read by pandas and by DuckDB with default settings:

python - <<'PY'
import pandas as pd
for f in ['pg_copy', 'pg_csv', 'sqlite', 'duckdb', 'mongo']:
    df = pd.read_csv('work/export/' + f + '.csv', parse_dates=['joined'])
    print(f"{f:8} rows {len(df)}  active {str(df.active.dtype):8} score {str(df.score.dtype):8} joined {str(df.joined.dtype)}")
PY
for f in pg_copy sqlite duckdb mongo; do printf '%-8s' $f; duckdb -noheader -list -c "SELECT count(*) || ' columns: ' || string_agg(column_type, ',') FROM (DESCRIBE SELECT * FROM read_csv('work/export/$f.csv'))" | tr -d '\n'; echo; done

pg_copy  rows 8  active str      score float64  joined datetime64[us, UTC]
pg_csv   rows 8  active str      score float64  joined datetime64[us, UTC]
sqlite   rows 8  active float64  score float64  joined datetime64[us]
duckdb   rows 8  active object   score float64  joined datetime64[us, UTC]
mongo    rows 8  active object   score float64  joined datetime64[us, UTC]
pg_copy 6 columns: BIGINT,VARCHAR,VARCHAR,BOOLEAN,DOUBLE,TIMESTAMP WITH TIME ZONE
sqlite  6 columns: BIGINT,VARCHAR,VARCHAR,BIGINT,DOUBLE,TIMESTAMP
duckdb  6 columns: BIGINT,VARCHAR,VARCHAR,BOOLEAN,DOUBLE,TIMESTAMP WITH TIME ZONE
mongo   8 columns: BIGINT,VARCHAR,VARCHAR,BOOLEAN,DOUBLE,TIMESTAMP WITH TIME ZONE,VARCHAR,VARCHAR

All five have 8 rows in pandas. The column types differ, because the spelling does. The boolean is str for both PostgreSQL files (t/f with a missing value), float64 for SQLite (1/0 with a missing value become 1.0/0.0), and object for DuckDB and MongoDB (true/false with a missing value). Only DuckDB gets a real boolean back from true/false, as a BOOLEAN, and it sniffed SQLite's integers as BIGINT. The timestamp is timezone-aware UTC when the file carries an offset (+00 or Z) and naive for SQLite, where the text has none. And score is float64 or DOUBLE everywhere, so 12.50 is no longer a string of five characters on the other side. If the column is money, tell the reader to read it as a decimal or as text.

How fast they are

The same 2,000,000 row orders table (about 130 MB as CSV) from each engine that could hold it, one run each, client and server on one machine:

MongoDB is not in the table because its data was a different set: 500,000 documents with five fields took 2.69 s. DuckDB's number comes from a columnar engine reading its own table with all cores, and the PostgreSQL number includes a sort. These are not a ranking of databases; they show that the built-in exporters are the fast path, and writing rows in a scripting language costs several times more. Exporting large tables has the memory figures, which matter more than the seconds.

Choosing

When the data is in PostgreSQL, use \copy for any file you keep and --csv for a look. When it is in MySQL, write the file from a client with a CSV library, unless the consumer is LOAD DATA. For SQLite, sqlite3 -csv -header with the row separator decided on purpose. In DuckDB, COPY ... TO. For MongoDB, mongoexport with a field list in version control, or JSON Lines if the documents are not flat. If the source is something else, query it through DuckDB or Python and write with a CSV library. Whatever the choice: set the formats in the query, write under a temporary name, count the records, keep a manifest. If two people on a team use different engines, agree on one reader's view of the file, such as pandas or DuckDB, and test each export against it, because the file that looks right in one tool is the one that surprises the next. The agreement belongs in the same place as the column list, and a sample file in the repository is worth more than a paragraph describing it.

Delimiters, quoting and header names

Three adjustments come up in every project: a semicolon instead of a comma (for spreadsheets in comma-decimal locales), quoting every field (for a fragile parser), and header names that read well. All three are options or aliases, and the engines differ in which they offer.

echo "psql:";    psql -c "\copy (SELECT id, name, score FROM ex_people WHERE id IN (1, 2) ORDER BY id) TO STDOUT WITH (FORMAT csv, HEADER, DELIMITER ';')"
echo "sqlite3:"; sqlite3 -csv -header -separator ';' work/export/ex.sqlite "SELECT id, name, score FROM ex_people WHERE id IN (1, 2) ORDER BY id"
echo "duckdb:";  duckdb work/export/ex.duckdb -c "COPY (SELECT id, name, score FROM ex_people WHERE id IN (1, 2) ORDER BY id) TO 'work/export/tmp_duck.csv' (FORMAT csv, HEADER, DELIMITER ';')" && cat work/export/tmp_duck.csv

psql:
id;name;score
1;Ana Silva;12.50
2;O'Neil, Pat;0.00
sqlite3:
id;name;score
1;"Ana Silva";12.5
2;"O'Neil, Pat";0.0
duckdb:
id;name;score
1;Ana Silva;12.50
2;O'Neil, Pat;0.00

PostgreSQL (DELIMITER ';'), SQLite (-separator ';') and DuckDB (DELIMITER ';') all took a delimiter, and with a semicolon the field O'Neil, Pat no longer needs quotes, since it contains no semicolon: PostgreSQL and DuckDB left it bare, while SQLite quoted it anyway, as it quotes most strings with spaces. mongoexport has no delimiter or quote option at all (its --help mentions neither), so a semicolon file from MongoDB means converting afterwards:

mongoexport --help 2>&1 | grep -iE "delim|terminat|quote" || echo "no delimiter or quote option in mongoexport --help"

no delimiter or quote option in mongoexport --help

To quote every field, PostgreSQL has FORCE_QUOTE * and DuckDB has FORCE_QUOTE (columns):

echo "psql FORCE_QUOTE *:"; psql -c "\copy (SELECT id, name, score FROM ex_people WHERE id IN (1, 6) ORDER BY id) TO STDOUT WITH (FORMAT csv, FORCE_QUOTE *)"
echo "duckdb FORCE_QUOTE:";  duckdb work/export/ex.duckdb -c "COPY (SELECT id, name, score FROM ex_people WHERE id IN (1, 6) ORDER BY id) TO 'work/export/tmp_duck.csv' (FORMAT csv, FORCE_QUOTE (id, name, score))" && cat work/export/tmp_duck.csv

psql FORCE_QUOTE *:
"1","Ana Silva","12.50"
"6","Ed","100.00"
duckdb FORCE_QUOTE:
"id","name","score"
"1","Ana Silva","12.50"
"6","Ed","100.00"

Both wrote "12.50" as a string, which is legal and tells a reader nothing about the type. In this run PostgreSQL's COPY printed no header because I did not ask for one, and DuckDB's did, so check the header option on every tool: the defaults differ. I found no quote-everything switch in SQLite's shell or in mongoexport.

Header names come from the query, which is the only place to set them reliably:

echo "psql:";    psql --csv -c "SELECT id AS \"Customer ID\", name AS \"Full name, as entered\" FROM ex_people WHERE id = 1"
echo "sqlite3:"; sqlite3 -csv -header work/export/ex.sqlite "SELECT id AS \"Customer ID\", name AS \"Full name, as entered\" FROM ex_people WHERE id = 1"
echo "duckdb:";  duckdb work/export/ex.duckdb -c "COPY (SELECT id AS \"Customer ID\", name AS \"Full name, as entered\" FROM ex_people WHERE id = 1) TO 'work/export/tmp_duck.csv' (FORMAT csv, HEADER)" && cat work/export/tmp_duck.csv
echo "mysql --batch:"; mysql -h mysql -uroot -psecret lab --default-character-set=utf8mb4 --batch -e "SELECT id AS \`Customer ID\`, name AS \`Full name, as entered\` FROM ex_people WHERE id = 1" 2>/dev/null

psql:
Customer ID,"Full name, as entered"
1,Ana Silva
sqlite3:
"Customer ID","Full name, as entered"
1,"Ana Silva"
duckdb:
Customer ID,"Full name, as entered"
1,Ana Silva
mysql --batch:
Customer ID	Full name, as entered
1	Ana Silva

An alias with a space and a comma, "Full name, as entered", was quoted in the header by PostgreSQL, SQLite and DuckDB, as any field would be; SQLite also quoted "Customer ID" because of the space. mysql --batch wrote the names as they are, tab-separated. A header with a space or a comma is legal, and it is the first thing a downstream script trips on. I use plain snake_case names in the query, and rename on the other side if a person needs a pretty header.

Text that looks like a formula

One more property is shared by every tool above, and it is a security one. A field that begins with =, +, - or @ is passed through untouched:

Q="SELECT '=1+1' AS a, '+SUM(A1:A9)' AS b, '-2+3' AS c, '@cmd' AS d"
echo "psql:";    psql --csv -c "$Q"
echo "mysql:";   mysql -h mysql -uroot -psecret lab --default-character-set=utf8mb4 --batch -e "$Q" 2>/dev/null
echo "sqlite3:"; sqlite3 -csv -header work/export/ex.sqlite "$Q"
duckdb work/export/ex.duckdb -c "COPY ($Q) TO 'work/export/tmp_duck.csv' (FORMAT csv, HEADER)" && echo "duckdb:" && cat work/export/tmp_duck.csv

psql:
a,b,c,d
=1+1,+SUM(A1:A9),-2+3,@cmd
mysql:
a	b	c	d
=1+1	+SUM(A1:A9)	-2+3	@cmd
sqlite3:
a,b,c,d
=1+1,+SUM(A1:A9),-2+3,@cmd
duckdb:
a,b,c,d
=1+1,+SUM(A1:A9),-2+3,@cmd

PostgreSQL, MySQL, SQLite and DuckDB wrote =1+1, +SUM(A1:A9), -2+3 and @cmd exactly as they received them, with no quoting and no marker. That is correct CSV. It is also the raw material for what is called CSV or formula injection: a spreadsheet that opens the file may treat such a field as a formula and evaluate it. If any column in your export holds text typed by someone else (names, notes, product titles), a user can plant a formula and a person in your organization will run it by opening the file. I did not test any spreadsheet here, so I cannot say how a given version treats these fields, and the OWASP page on CSV injection is where the mitigations are discussed. The usual one is to prefix a risky field with a single quote or a space on export, which changes the data and so belongs in the contract. If the file is for programs, ignore the whole topic; if it is for people with spreadsheets, decide it on purpose.

The commands I use

A summary of what I would type for each engine, every one of which was run for this guide or one of the articles it links to:

PostgreSQL  psql -c "\copy (SELECT ...) TO 'out.csv' WITH (FORMAT csv, HEADER)"
MySQL       a short script: streaming cursor + the csv module   (or mysql --batch --quick for TSV)
SQLite      sqlite3 -csv -header app.db "SELECT ..." > out.csv  (CRLF; use tr -d '\r' for LF)
DuckDB      duckdb -c "COPY (SELECT ...) TO 'out.csv' (FORMAT csv, HEADER)"
MongoDB     mongoexport --db d -c c --type=csv --fields a,b,c --sort '{"_id": 1}' --out out.csv

For PostgreSQL I keep \copy and not --csv whenever the file will be loaded again, because of the NULL difference above. For MySQL, the script is the only option that read back 8 of 8 and wrote the file where I ran it. For SQLite I decide the row ending at the moment I write the command, not afterwards. For DuckDB there is nothing to add. For MongoDB the field list is in a file under version control.

Before you ship an export

A short checklist, in the order I run it:

1. The query formats every date, timestamp and boolean explicitly.

2. The file was written under a temporary name and renamed on success, with the exit status checked.

3. The record count in the file, read with a CSV parser, equals the count from the source.

4. The awkward-rows table, exported with this exact recipe, reads back exactly.

5. NULL and the empty string are written the way the consumer expects, and the contract says which.

6. The row ending and encoding match what the consumer needs.

7. A size, row count and checksum travel with the file.

None of these takes long once the export is scripted, and each one is the answer to a failure that happened somewhere I have worked.

What is not covered

SQL Server (bcp, sqlcmd) and Oracle (SQLcl, SQL*Plus) are missing from this guide. The editorial rule of this site is that every command is run and every output shown is the output I got. As far as I can tell, running either engine locally means accepting the vendor's license terms in a container image or a download, and that is a decision for whoever owns the project, not for a test script. When it is made, they will get the same treatment: the awkward table, the read-back and the failure modes. Until then, a vendor page is a better source than a guess, and for either engine I would run the awkward table through the recipe before trusting it.

How to tell it worked

Export the awkward table with your recipe and read it back with the parser the consumer will use, not only with the one you have. Count the records and compare with the table. Check the line endings with od -c or cat -A. Check that NULL and the empty string come out the way the contract says. Break the export on purpose, with a bad query or a bad host, and look at what is left on disk and at the exit status. Do all of this once, when the export is built, and again when a database or tool version changes.

When CSV is the wrong deliverable

A CSV loses types, loses the difference between missing and empty in half the tools above, and has no way to say what encoding it is in. If the consumer is a program that reads Parquet or JSON Lines, send that: Parquet keeps the types and compresses far better, and JSON Lines keeps nulls and nesting. If the consumer is another database of the same kind, use its native dump or replication format, which carries types, constraints and permissions that CSV cannot. CSV is the right choice when the reader is a person with a spreadsheet, a tool that only speaks CSV, or a hand-off between organizations that agreed on nothing else. In those cases, do the decisions above on purpose.