Exporting a PostgreSQL Query to CSV With psql and COPY

Almost every PostgreSQL export starts the same way: you have a query, someone wants the rows in a file, and the file has to open correctly in whatever reads it next. psql can do this in three different ways, and they are not equivalent. They differ in where the file ends up, in who is allowed to write it and in how they spell a missing value. This article exports a small table built to be awkward, compares the three, and finishes on what happens when an export fails halfway, which is the part that causes incidents.

The three ways

1. **\copy (query) TO file** is a psql meta-command. The file is written by the psql client, on the machine where psql runs, with your permissions. The server only streams the rows.

2. **COPY (query) TO 'file'** runs on the server and writes on the server's disk. It needs elevated privileges and is rarely what you want.

3. **psql --csv -c "query"** prints the result set as CSV to standard output. It is the quick way to see something and redirect it.

The manual describes COPY and the \copy meta-command in detail. In practice I use \copy for any export I will keep, --csv for a look, and I have not used server-side COPY TO a file in years.

The basic export

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

TO STDOUT sends the CSV to the terminal here; replace it with a file name (TO 'ex_people.csv') to write a file. FORMAT csv selects CSV rules, with quotes around any field that contains the delimiter, a quote or a newline, and quote characters doubled. HEADER writes the column names first. The result is the same text you would write by hand: "O'Neil, Pat" quoted for its comma, "Bo ""Bee"" Chen" for its quotes, and the note with a newline inside quotes spanning two lines. Reading the file back with Python's csv module returned all eight name and note pairs exactly. The export is a SELECT, so every transformation PostgreSQL can do is available before the row leaves: casts, to_char, joins, filters.

\copy takes the query in parentheses on one line. There is no semicolon inside, and the whole of the rest of the line belongs to \copy.

NULL and the empty string are different things

The most useful difference between \copy and --csv is how they write a missing value. Two rows: id 5 has an empty string in note and id 6 has NULL.

psql -At --csv -c "SELECT id, note FROM ex_people WHERE id IN (5, 6) ORDER BY id"
echo ---
psql -c "\copy (SELECT id, note FROM ex_people WHERE id IN (5, 6) ORDER BY id) TO STDOUT WITH (FORMAT csv)"

5,
6,
---
5,""
6,

psql --csv wrote 5, and 6,: both are an empty field, and the information is gone. \copy wrote 5,"" and 6,. In CSV mode, COPY writes NULL as an unquoted empty field and an empty string as a quoted empty string, so a reader that can tell them apart still can. PostgreSQL's own COPY FROM reads them back as NULL and an empty string, which makes a round trip back into PostgreSQL lossless; plenty of other readers treat both as missing. If the consumer must not guess, ask COPY for an explicit marker with the NULL option.

Options worth knowing

psql -c "\copy (SELECT id, name, score, active FROM ex_people WHERE id <= 3 ORDER BY id) TO STDOUT WITH (FORMAT csv, HEADER, DELIMITER ';', FORCE_QUOTE *)"
echo ---
psql -c "\copy (SELECT id, note, score FROM ex_people WHERE id IN (5, 6) ORDER BY id) TO STDOUT WITH (FORMAT csv, NULL 'NULL')"

id;name;score;active
"1";"Ana Silva";"12.50";"t"
"2";"O'Neil, Pat";"0.00";"f"
"3";"Bo ""Bee"" Chen";"7.00";"t"
---
5,,3.14
6,NULL,100.00

The first export changed the delimiter to a semicolon, which many spreadsheets in comma-decimal locales expect, and used FORCE_QUOTE * to quote every field. The result keeps the numbers quoted ("12.50"), which is legal but makes some readers treat them as text. Use it when a downstream parser is fragile and not otherwise. The second export used NULL 'NULL': the missing note in row 6 became the word NULL, and the empty string in row 5 stayed empty. Pick a marker that cannot appear in real data. Other options for exporting: QUOTE, ESCAPE and ENCODING; FORCE_NULL and FORCE_NOT_NULL apply when loading.

Encoding

PostgreSQL stores this database in UTF-8 and COPY writes UTF-8 unless you ask for something else. When a consumer insists on Latin-1:

psql -c "\copy (SELECT id, name FROM ex_people WHERE id IN (1, 7) ORDER BY id) TO STDOUT WITH (FORMAT csv, ENCODING 'LATIN1')" | xxd | head -n 3
psql -c "\copy (SELECT id, note FROM ex_people WHERE id IN (1, 7) ORDER BY id) TO STDOUT WITH (FORMAT csv, ENCODING 'LATIN1')" 2>&1 | head -n 4

00000000: 312c 416e 6120 5369 6c76 610a 372c 5a6f  1,Ana Silva.7,Zo
00000010: eb20 dc6e 616c 0a                        . .nal.
1,plain
ERROR:  character with byte sequence 0xe2 0x9c 0x93 in encoding "UTF8" has no equivalent in encoding "LATIN1"

The first command converted Zoë Ünal: the hex dump shows eb for ë and dc for Ü, single bytes, as Latin-1 uses. The second tried to convert a note that contains a check mark, which Latin-1 has no character for, and the export stopped with character with byte sequence 0xe2 0x9c 0x93 in encoding "UTF8" has no equivalent in encoding "LATIN1". There is no lossy mode: it fails, which is the right behavior. Convert in the query (regexp_replace, translate) only if you know what to do with the characters that do not fit. The mojibake article covers what happens downstream when the consumer guesses the wrong encoding.

Why server-side COPY is usually the wrong choice

COPY ... TO 'file' with a path is run by the server process and writes where the server can write. For an ordinary role:

psql -q -c "DROP ROLE IF EXISTS exporter" -c "CREATE ROLE exporter LOGIN PASSWORD 'x'" -c "GRANT SELECT ON ex_people TO exporter" 2>&1 | grep -v NOTICE
PGUSER=exporter PGPASSWORD=x psql -c "COPY (SELECT * FROM ex_people) TO '/tmp/ex_people.csv' WITH (FORMAT csv)" 2>&1
PGUSER=exporter PGPASSWORD=x psql -c "\copy (SELECT id, name FROM ex_people WHERE id <= 2 ORDER BY id) TO STDOUT WITH (FORMAT csv)" 2>&1

ERROR:  permission denied to COPY to a file
DETAIL:  Only roles with privileges of the "pg_write_server_files" role may COPY to a file.
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.
1,Ana Silva
2,"O'Neil, Pat"

The error is explicit: permission denied to COPY to a file, only roles with pg_write_server_files may do it. The hint says what to use: "psql's \copy command also works for anyone." The last command in the block, \copy ... TO STDOUT as the same limited role, returned two rows. Beyond privileges, a server-side file is on the database host, which is usually not the machine where you want it, and on a managed service you often cannot read it at all. Use \copy, or COPY ... TO STDOUT through any client library.

Parameters: the thing that does not work

A script usually exports with a parameter, such as a date or a minimum id. psql has variables, and the obvious attempt fails in two of the three places you would try it:

psql -v min=7 -c "\copy (SELECT id, name FROM ex_people WHERE id >= :min ORDER BY id) TO STDOUT WITH (FORMAT csv)" 2>&1 | head -n 3
echo ---
psql -v min=7 -c "COPY (SELECT id, name FROM ex_people WHERE id >= :min ORDER BY id) TO STDOUT WITH (FORMAT csv)" 2>&1 | head -n 3
echo ---
echo "COPY (SELECT id, name FROM ex_people WHERE id >= :min ORDER BY id) TO STDOUT WITH (FORMAT csv);" | psql -v min=7 2>&1 | head -n 3

ERROR:  syntax error at or near ":"
LINE 1: ...OPY  ( SELECT id, name FROM ex_people WHERE id >= :min ORDER...
                                                             ^
---
ERROR:  syntax error at or near ":"
LINE 1: COPY (SELECT id, name FROM ex_people WHERE id >= :min ORDER ...
                                                         ^
---
7,Zoë Ünal
8,Fay

\copy with :min gave syntax error at or near ":". The manual says why: unlike most meta-commands, the rest of the line is taken as arguments to \copy and variables are not interpolated. psql -c "COPY ... :min ..." failed the same way. The third form, the statement sent through standard input with -v min=7, worked and returned ids 7 and 8. When a script needs a parameter, either pipe the SQL into psql, or build the query string in the shell and quote the value yourself, with the injection risk that implies for anything that is not a number you control.

When the export fails

The case that bites is a query that errors after the output has started. Id 5 divides by zero:

rm -f work/export/partial.csv
psql -c "\copy (SELECT id, 10 / (id - 5) AS ratio FROM ex_people ORDER BY id) TO 'work/export/partial.csv' WITH (FORMAT csv, HEADER)"; echo "psql exit status: $?"
echo "file left behind:"; ls -l work/export/partial.csv | awk '{print $5 " bytes"}'; cat work/export/partial.csv

ERROR:  division by zero
psql exit status: 1
file left behind:
9 bytes
id,ratio

psql exited with status 1 and printed the error, and it left a file behind. The file is 9 bytes and contains only the header row. It looks like an export of a table with no data. A downstream job that checks only that the file exists, or that picks up whatever is in the folder at 6 a.m., has just consumed an empty report. The same failure through a pipe shows the other trap:

psql --csv -c "SELECT id, 10 / (id - 5) AS ratio FROM ex_people ORDER BY id" | cat; echo "pipeline status: ${PIPESTATUS[0]} ${PIPESTATUS[1]}"

ERROR:  division by zero
pipeline status: 1 0

psql exited 1 and cat exited 0, so the pipeline's status was 0 unless pipefail is set. Two habits fix both. Write to a temporary name and rename on success, so a failed export never has the real name. And in scripts, set set -euo pipefail, then check the exit status of the thing that matters. The nightly export article puts both into one script.

How to tell it worked

Read the file back with a real CSV parser and compare the row count with SELECT count(*) run in the same moment. Compare the column count in every record with the header, which is what the counting article does. Open the file with cat -A and look at the line endings, because \copy writes \n, and Windows consumers may want \r\n. Look at the awkward rows by eye: the quoted comma, the newline and the non-ASCII row. And test the failure path, not only the happy one, by running the export against a query that errors and seeing what is left on disk.

When psql is the wrong tool

For a few hundred megabytes, psql is perfect. For tables with hundreds of millions of rows or an export that must run in parallel, you will want to stream through a client library with a server-side cursor, partition the query, or compress on the fly, as covered in exporting large tables. When a consumer needs formatted numbers, particular date styles or fixed widths, the CSV is not the right container, and a view that formats the columns is the best compromise. And when the data contains personal information, the first question is whether it should be in a file at all.