PostgreSQL Copy to CSV: Efficient Data Export Guide
Learn how to export data from PostgreSQL to CSV using COPY and psql. This guide covers syntax, practical examples, and best practices for reliable CSV exports, including server-side and client-side approaches, handling large datasets, and common pitfalls.
PostgreSQL copy to CSV is the server-side method to export data directly into a CSV file or to stdout. It supports standard CSV options like HEADER, DELIMITER, and ENCODING, and works with a full table or a query. This article covers syntax, practical examples, pitfalls, and best practices for reliable CSV exports from PostgreSQL.
What is PostgreSQL COPY to CSV and when to use it
PostgreSQL COPY to CSV is a high-performance, server-side mechanism to export data from a table or the result of a query into a CSV file. It runs on the database server, which means the target file must be on a filesystem reachable by the server process. This is ideal for scheduled exports, data warehouse pipelines, or sharing data with downstream tools that expect CSV. In contrast, client-side methods (like \copy in psql) bring data to your machine, which can be safer but slower for large datasets. When planning an export, consider permissions, the destination path, and whether you need a strict header row. This section demonstrates the basic approach and common options.
COPY public.sales_orders TO '/var/lib/postgres/data/sales_orders.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8');Explanation:
- FORMAT csv enables CSV formatting;
- HEADER adds column names as the first row;
- ENCODING ensures character compatibility.
- The path must be writable by the server OS user.
- Variations: you can omit HEADER or change DELIMITER to a different character. For many teams, this is the standard 'postgresql copy to csv' workflow.
Basic syntax for exporting a full table
To export an entire table, point COPY at the table and provide a destination path that the server can write to. You can fine-tune the delimiter and whether to include a header. The example below exports the public.customers table to a CSV file on the server. For strict compatibility, set UTF-8 encoding and consider quoting.
COPY public.customers TO '/tmp/customers.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',');Notes:
- If your table has a large number of rows, consider exporting in chunks or using STDOUT with client-side redirection.
- Ensure the server process owner has write permissions to the target directory.
Copying a query result to CSV
Often you don't want the entire table but a filtered or transformed subset. COPY supports exporting the result of a SELECT. This is useful for daily extracts or restricted datasets. The example exports id, email, and created_at for users created after 2024-01-01.
COPY (SELECT id, email, created_at FROM public.users WHERE created_at >= DATE '2024-01-01') TO '/tmp/users_2024.csv' WITH (FORMAT csv, HEADER true);Note: The column order in the SELECT determines the CSV column order. You can also rename columns in the SELECT for clarity in downstream tools.
Copy to client with psql: using \copy
When you need the CSV on your local workstation, use the client-side \copy meta-command in psql. This reads data from the database and writes directly to a local file, bypassing any server file permissions. The example below demonstrates exporting with UTF-8 encoding and a header.
psql -U myuser -d mydb -c '\copy (SELECT id, name FROM public.customers) TO '/tmp/customers.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8')'Tip: Use a local path that your user account can write to. If you can't access a local filesystem, pipe the output to a file or stream it to another process.
Handling large exports with streaming and chunking
Large datasets pose memory and time challenges. Instead of exporting a massive single file, stream data in chunks or export to stdout and redirect, then concatenate. You can run a server-side COPY to STDOUT and redirect in the shell, or partition data by key ranges. The following examples show both approaches.
# Server-side COPY to STDOUT, then redirect to a local file
psql -d mydb -c "COPY (SELECT * FROM large_table WHERE id BETWEEN 1 AND 100000) TO STDOUT WITH CSV HEADER" > /tmp/large_part1.csv# Chunked export loop (bash) - appends parts to a single file
for i in {0..9}; do
start=$((i*100000 + 1))
end=$(((i+1)*100000))
psql -d mydb -c "COPY (SELECT * FROM large_table WHERE id BETWEEN $start AND $end) TO STDOUT WITH CSV HEADER" >> /tmp/large_dump.csv
doneBest practice: choose an appropriate chunk size to balance parallelism and I/O throughput. Consider adding a progress log.
Common pitfalls and troubleshooting
Exporting to CSV is straightforward, but several pitfalls can derail a job. The most common issues relate to filesystem permissions, server vs client execution, and incorrect escaping. Always verify the destination path exists and the PostgreSQL OS user has write access. For local exports, prefer \copy to avoid needing server-side file access. If you encounter permission errors, export to STDOUT and redirect on the client. Finally, test with a small subset to confirm column order and data types.
# Quick check of filesystem permissions
ls -ld /var/lib/postgres/data-- If the server cannot write to a file, export to STDOUT and redirect on the client
psql -d mydb -c "COPY (SELECT * FROM t) TO STDOUT WITH CSV HEADER" > /tmp/t.csvBest practices for clean CSV outputs
To ensure compatibility across tools, adopt a consistent CSV configuration: use a single delimiter, set HEADER to include column names, and choose UTF-8 encoding. Explicitly handle NULLs to prevent misalignment in downstream processes. Prefer explicit column lists over SELECT * to avoid accidental column order changes. Finally, validate the resulting CSV with a simple parser or a quick import check into a spreadsheet or database tool.
COPY public.orders (order_id, customer_id, total) TO '/tmp/orders.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',', NULL '', ENCODING 'UTF8');Security, auditing, and compliance considerations
Exports may expose sensitive data if access controls are lax. Apply the principle of least privilege on the exporting role and audit CSV exports. Use role-based access controls to restrict who can run export queries, and consider masking or redacting sensitive fields when appropriate. For reproducibility, store export scripts in version control and log each run with timestamp, job ID, and destination path.
-- Example: check who can export data
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'orders';Steps
Estimated time: 15-45 minutes
- 1
Identify the export target
Decide whether you export a full table or the results of a query. Consider permissions and the destination path.
Tip: Verify file path with OS permissions. - 2
Choose export method
Decide between COPY (server-side) and \copy (client-side).
Tip: Server-side COPY is faster but requires server access. - 3
Prepare the export
Draft your SELECT statement if exporting a subset; ensure columns match target CSV structure.
Tip: Avoid SELECT * in production; list explicit columns. - 4
Run the export
Execute the COPY command and capture the output file.
Tip: Ensure database user has write permission. - 5
Validate and clean CSV
Check the CSV for correct headers, delimiter, and quoting; handle NULLs.
Tip: Test with a small sample first. - 6
Automate (optional)
Wrap in a script or cron job for regular exports.
Tip: Log export results and errors.
Prerequisites
Required
- Required
- OS user with write access to the server filesystemRequired
- Required
- Basic SQL knowledge (SELECT, WHERE)Required
- Command-line or terminal accessRequired
Optional
- Optional: scheduling tool (e.g., cron)Optional
Commands
| Action | Command |
|---|---|
| Copy table to CSV (server-side)Server-side export; destination path must be writable by the server user | COPY public.table TO '/path/file.csv' WITH (FORMAT csv, HEADER true) |
| Copy query to CSV (server-side)Export only required columns/rows | COPY (SELECT * FROM public.table WHERE condition) TO '/path/file.csv' WITH (FORMAT csv, HEADER true) |
| Export to client (psql)Run within psql; uses client filesystem | \copy (SELECT * FROM public.table) TO '/path/file.csv' WITH (FORMAT csv, HEADER true) |
People Also Ask
What is COPY in PostgreSQL and how does it relate to CSV?
COPY is a SQL command that exports data in formats including CSV. For CSV output, specify FORMAT csv and optional HEADER. It can export a table or a query. Use \copy for client-side access.
COPY exports data as CSV by running a SQL command. Include FORMAT csv and HEADER to align columns. Use \copy when you need client-side access.
When should I use COPY vs. \copy?
Use COPY for server-side exports when the server has filesystem access to the destination path. Use \copy when exporting to your local machine via a client like psql. The latter runs on the client and is generally safer for local workloads.
Use COPY for server-side writes, \copy for client-side writes.
What about exporting large datasets safely?
Split exports into chunks or export to stdout and redirect on the client to avoid memory pressure. Use a WHERE clause to partition data or export in batches.
For large exports, partition data or stream to avoid memory issues.
How do I handle quotes and commas in CSV?
CSV mode handles quoting automatically when using FORMAT csv. PostgreSQL escapes quotes by doubling them. Use DELIMITER and ENCODING to ensure compatibility.
CSV export handles quotes automatically; PostgreSQL escapes them as needed.
Are there security or permissions considerations?
Ensure the PostgreSQL server process user has write permissions to the destination path. Avoid exposing server-side paths in logs. Use parameterized queries to avoid SQL injection when building dynamic queries.
Be mindful of filesystem permissions and avoid exposing server paths.
Can I export to a network share or external storage?
Yes, if the PostgreSQL server can access the network path and the OS user has write permissions. Network latency may affect performance.
Exports can go to network shares if the server can reach the path.
Main Points
- Use COPY for server-side CSV exports
- Choose between COPY and \copy based on permissions
- Always include HEADER for column alignment
- Test with small data before large exports