Export Table to CSV File

PostgreSQL offers two commands for exporting table data to CSV: the server-side COPY ... TO statement and the client-side psql \copy command. The server-side command writes the file directly on the database host and requires superuser access; the client-side command streams data to a file on your local machine using ordinary user privileges.

Exporting with the COPY statement

Assume the persons table contains the following data:

SELECT * FROM persons;

 id | first_name | last_name |    dob     |              email
----+------------+-----------+------------+---------------------------------
  1 | John       | Doe       | 1995-01-05 | [email protected]
  2 | Jane       | Doe       | 1995-02-05 | [email protected]

Export the entire table to a CSV file with a header row:

COPY persons
TO '/var/lib/postgresql/persons_db.csv'
DELIMITER ','
CSV HEADER;

COPY 2

Export only selected columns:

COPY persons (first_name, last_name, email)
TO '/var/lib/postgresql/persons_partial_db.csv'
DELIMITER ','
CSV HEADER;

Export just the email column without a header:

COPY persons (email)
TO '/var/lib/postgresql/persons_email_db.csv'
DELIMITER ','
CSV;

The output file must be writable by the OS user that runs the PostgreSQL server process. The path is resolved on the server, not the client machine.

Exporting a query result with copy

When you need the file on your local machine or lack superuser rights, use the psql \copy meta-command. It accepts an arbitrary SELECT query as the source:

copy (SELECT * FROM persons) TO '/Users/you/persons_client.csv' WITH CSV

You can filter and shape the export with any SELECT syntax:

copy (
  SELECT first_name, last_name, email
  FROM persons
  ORDER BY last_name, first_name
) TO '/Users/you/persons_sorted.csv' WITH (FORMAT csv, HEADER true)

Production tips for exporting CSV data

• Wrap a subquery in COPY (...) TO to filter, join, or transform data before writing — you are not limited to exporting a whole table.
• Always include HEADER when the CSV will be consumed by tools that expect column names in the first row.
• Add an ORDER BY inside the subquery to produce a deterministic file, which helps with diffing exports across runs.
• For large exports, run COPY outside of an explicit transaction to avoid holding a snapshot lock for the duration of the write.
• Use \copy in scripts and CI pipelines rather than COPY to avoid hard dependencies on server-side file paths.