Exporting Query Results to CSV in Postgres Using COPY and psql

You've written a query, you have the data you need, and now someone wants it as a CSV file. Maybe it's a stakeholder report, a data handoff, or a quick sanity check in Excel. Postgres gives you two solid ways to do this: the server-side COPY command and the client-side \copy meta-command inside psql. Knowing which one to use — and how to use it correctly — saves you a lot of fumbling.

What you'll learn

• The difference between server-side COPY and client-side \copy
• How to export a full table or an arbitrary query result to CSV
• How to control headers, delimiters, and NULL handling
• How to script CSV exports from the command line without opening a psql session
• Common mistakes that cause silent data corruption or permission errors

Prerequisites

You need a running Postgres instance (any modern version works), access to psql, and a user account with at least SELECT on the tables you're querying. For server-side COPY TO, your Postgres superuser or a role with the pg_write_server_files privilege is required. If you don't have that, the client-side \copy approach is your friend.

COPY vs \copy: What's the Difference?

This distinction trips up a lot of people, so it's worth being explicit up front.

Server-side COPY is a SQL command executed by the Postgres server process. When you write a file path, the server writes to that path on the server's filesystem, not your laptop. It requires superuser or file-write privileges, and it's fast because no data travels over the client connection.

Client-side \copy is a psql meta-command (note the backslash). It sends rows over the client connection and writes the file on the client's machine — the one running psql. It works with any regular database user, and it's the right default for most day-to-day use.

If you're connecting to a remote Postgres server and want the CSV on your local machine, always use \copy. Using COPY TO will write the file on the remote server, where you probably can't read it.

Exporting a Full Table with \copy

Open a psql session and run:

\copy orders TO '/tmp/orders.csv' WITH (FORMAT CSV, HEADER);

That exports every row and column from the orders table. The HEADER option writes column names as the first line, which is almost always what you want.

A few things to note about the path: on Linux and macOS, use an absolute path or a path relative to where you launched psql. On Windows, use forward slashes or escape backslashes: 'C:/Users/you/exports/orders.csv'.

Exporting an Arbitrary Query

You're rarely exporting a whole table unfiltered. Most of the time you have a SELECT with joins, filters, or computed columns. Wrap the query in parentheses:

\copy (
  SELECT
    o.id,
    o.created_at,
    c.name AS customer_name,
    o.total_amount
  FROM orders o
  JOIN customers c ON c.id = o.customer_id
  WHERE o.created_at >= '2024-01-01'
  ORDER BY o.created_at
) TO '/tmp/q1_orders.csv' WITH (FORMAT CSV, HEADER);

The query runs on the server, and only the result set travels to the client to be written to disk. Column names in the header come from the SELECT list, so aliases like customer_name show up exactly as written.

Using Server-Side COPY When You Have the Permissions

If you're running psql directly on the database host, or you have superuser access and want the file written server-side, the syntax is nearly identical — just drop the backslash and use plain SQL:

COPY (
  SELECT id, created_at, total_amount
  FROM orders
  WHERE status = 'shipped'
) TO '/var/exports/shipped_orders.csv' WITH (FORMAT CSV, HEADER);

You can also use COPY table_name TO ... without a query for full table exports. The server-side version can also write to STDOUT, which is useful in shell pipelines — covered below.

Controlling the Output Format

The WITH clause accepts several options that let you shape the output precisely.

Delimiter

The default delimiter is a comma. To use a tab (common for TSV files) or any other character:

\copy orders TO '/tmp/orders.tsv' WITH (FORMAT CSV, HEADER, DELIMITER E'\t');

NULL Handling

By default, NULL values appear as empty fields. If a downstream system expects a literal string like NULL or N/A, set it explicitly:

\copy orders TO '/tmp/orders.csv' WITH (FORMAT CSV, HEADER, NULL 'NULL');

Quote Character

Postgres uses double quotes by default to wrap fields that contain the delimiter or newlines. You can change it, though the default is almost universally compatible:

\copy orders TO '/tmp/orders.csv' WITH (FORMAT CSV, HEADER, QUOTE '''');

That's a single quote wrapped in four single quotes — the outer two delimit the SQL string, the inner two produce a literal single quote character.

Force Quoting

If the receiving system is strict about quoting all text columns regardless of content, use FORCE_QUOTE:

\copy orders TO '/tmp/orders.csv'
  WITH (FORMAT CSV, HEADER, FORCE_QUOTE (customer_name, status));

Running a CSV Export Without Entering psql Interactively

For scripts and automation, you don't want to open an interactive session. Pass the \copy command directly via the -c flag or a here-string.

Using -c:

psql -U myuser -d mydb -c "\copy orders TO '/tmp/orders.csv' WITH (FORMAT CSV, HEADER);"

If your query is long, write it to a .sql file and use -f:

psql -U myuser -d mydb -f export_orders.sql

Where export_orders.sql contains your full \copy (...) TO ... WITH (...) statement. This approach keeps your scripts readable and version-controllable.

Piping to stdout for Shell Pipelines

When you want to pipe CSV output through other Unix tools (like gzip or aws s3 cp), redirect to stdout:

psql -U myuser -d mydb -c "\copy orders TO STDOUT WITH (FORMAT CSV, HEADER);" \
  | gzip > /tmp/orders.csv.gz

The STDOUT keyword works with both \copy and server-side COPY. This is one of the cleanest ways to stream large exports directly into compression or object storage without touching disk on the client.

Handling Large Exports

For tables with millions of rows, a few practices keep things sane.

First, add an explicit ORDER BY only if you need deterministic row order. Sorting a large result set costs time and memory. If downstream consumers don't care about order, omit it.

Second, consider exporting in batches using WHERE on a date column or an integer ID range. This lets you resume if something fails mid-export, and it avoids holding a long-running transaction open.

Third, watch your disk space. A table that occupies several gigabytes in Postgres will often produce a similarly sized CSV, sometimes larger because binary storage is more compact than text. Check available space before starting.

Common Pitfalls

Permission denied errors with server-side COPY — This almost always means you're running COPY TO (no backslash) as a non-superuser. Switch to \copy instead, or ask your DBA to grant pg_write_server_files.

The file appears on the wrong machine — A classic mistake: you ran COPY TO '/tmp/output.csv' on a remote server and can't find the file locally. The file is on the server. Use \copy (backslash) to write locally.

Encoding issues — If your data contains non-ASCII characters and the CSV looks garbled in Excel, check the client encoding. Set it explicitly before exporting:

SET client_encoding = 'UTF8';

Excel on Windows sometimes expects Latin-1 or UTF-8 with a BOM. You can add a BOM manually or use a tool like iconv to re-encode after export.

Newlines inside field values — Postgres correctly quotes fields containing newlines in CSV mode, but some poorly written CSV parsers choke on multi-line fields. If the receiving system is fragile, strip or replace newlines in your query using REPLACE(column, E'\n', ' ') before exporting.

Header column names don't match expectations — The header row uses the column names or aliases from your SELECT list. If a downstream script expects specific column names, use explicit aliases in your query rather than relying on the table's column names.

Wrapping Up

Exporting CSV from Postgres is straightforward once you understand the server-side versus client-side split. Here's what to do next:

• Try a basic \copy table TO '/tmp/test.csv' WITH (FORMAT CSV, HEADER); export right now to confirm your setup works.
• Convert any existing COPY commands in your scripts to \copy unless you're sure the file should land on the server.
• Add NULL, DELIMITER, and FORCE_QUOTE options incrementally as downstream consumers reveal their requirements.
• For automation, write your export statements to .sql files and invoke them with psql -f so they're easy to review and version-control.
• For large or recurring exports, explore pg_dump or tools like pgcopy if you need parallelism or incremental logic beyond what a single \copy statement can offer.