How to Export Data to CSV in PostgreSQL Easily

In PostgreSQL, you can export data to a CSV file using the COPY command. Use COPY table_name TO 'file_path' WITH CSV HEADER; to export the table data with column headers to a CSV file.

Syntax

The basic syntax to export data to a CSV file in PostgreSQL is:

• COPY table_name TO 'file_path' WITH CSV HEADER;
• table_name: The name of the table or query to export.
• file_path: The full path where the CSV file will be saved on the server.
• WITH CSV HEADER: Exports the data in CSV format and includes the column names as the first row.

sql

COPY table_name TO '/path/to/file.csv' WITH CSV HEADER;

Example

This example exports all rows from the employees table to a CSV file named employees.csv with column headers included.

sql

COPY employees TO '/tmp/employees.csv' WITH CSV HEADER;

Output

COPY 5

Common Pitfalls

Common mistakes when exporting to CSV in PostgreSQL include:

• Trying to export to a file path where the PostgreSQL server does not have write permission.
• Using a client-side path instead of a server-side path (the file path is relative to the database server machine).
• Not including WITH CSV HEADER if you want column names in the CSV.
• Using SELECT ... TO instead of COPY, which is invalid syntax.

sql

/* Wrong: Using SELECT TO (invalid) */
SELECT * TO '/tmp/data.csv' FROM employees;

/* Right: Use COPY */
COPY employees TO '/tmp/data.csv' WITH CSV HEADER;

Quick Reference

Command	Description
COPY table_name TO 'file_path' WITH CSV HEADER;	Export table data to CSV with column headers.
COPY (SELECT * FROM table_name WHERE condition) TO 'file_path' WITH CSV HEADER;	Export query results to CSV with headers.
\copy table_name TO 'file_path' CSV HEADER	Client-side export using psql command line tool.
WITH CSV	Exports data in CSV format.
WITH CSV HEADER	Includes column names as first row.

Key Takeaways

Use the COPY command to export PostgreSQL data to CSV files on the server.

Include WITH CSV HEADER to add column names to the exported CSV.

File paths in COPY are relative to the database server, not your local machine.

Use \copy in psql for client-side CSV export if you lack server file access.

Check file permissions to avoid write errors during export.