阅读中文版本

Export a Large Table to CSV Fast

Use COPY TO with a server-side file or stdout stream to export large tables far faster than row-by-row SELECT.

psql -c "COPY (SELECT * FROM orders WHERE created_at >= now() - interval '1 day') \
  TO STDOUT WITH (FORMAT csv, HEADER true)" > orders_today.csv

head -1 orders_today.csv
wc -l orders_today.csv

What this recipe does

Export a large result set to CSV using COPY instead of row iteration.

A nightly export of millions of rows takes hours because the script fetches rows one at a time.

Prerequisites

• PostgreSQL with COPY privileges
• Disk or stdout destination
• A query or table to export

Procedure

1. Stream the export via COPY to stdout with a custom delimiter.

psql -c "COPY (SELECT * FROM orders WHERE created_at >= now() - interval '1 day') \
  TO STDOUT WITH (FORMAT csv, HEADER true)" > orders_today.csv

Expected result: The CSV is written efficiently in a single round trip instead of per-row fetches.

2. Validate row count and header.

head -1 orders_today.csv
wc -l orders_today.csv

Expected result: The header is present and the line count matches the expected row count plus one.

How to confirm it worked

Run the commands above and confirm every expected result in order. If a step does not produce the stated output, use the troubleshooting notes before continuing. A passing run shows the export finishes quickly, the header and row count are correct, and consumers parse the file without errors.

Troubleshooting

NULL rendered unexpectedly — Set NULL marker explicitly, for example with NULL ‘’ when consumers expect an empty field.

Encoding issues — Force client encoding to UTF-8 and verify with file or iconv.

Limitations

• COPY does not apply arbitrary per-row transformation; do it in the query.
• CSV cannot represent nested structures.
• Large exports still consume server I/O.