Beyond Import: How to Output and Download a Postgres Table as CSV

Moving data into a database is only half the job. Whether you're generating a weekly stakeholder report, creating a backup, or pulling data into Python for analysis, knowing how to export a Postgres table to CSV is equally essential.

PostgreSQL provides the same command pair for both directions: server-side COPY for maximum speed, and client-side \copy for cloud and managed instances. If you're more familiar with the import side, the complete import guide covers the same commands in the inbound direction.

1. The Local Download: Using \copy in psql

If you're working with a remote server or a cloud instance like AWS RDS, you want to download a Postgres table as CSV directly to your local machine. The \copy meta-command is the right tool — it creates the file on your computer, not the database server's disk, so no server-level write permissions are required.

psql -d my_database -U my_user \
  -c "\copy target_table TO 'my_local_data.csv' WITH (FORMAT CSV, HEADER);"

• No permissions needed on the server — it uses your local user credentials.
• Remote-ready — the standard export path for RDS, Cloud SQL, and any managed Postgres where you lack SSH access.

The same SSH tunnel trick used for remote CSV imports works identically for exports — tunnel port 5432 locally, then run \copy TO through the tunnel.

2. High-Speed Server Export: The COPY Command

If you have direct access to the database server's filesystem and need to export a large table as fast as possible, the SQL COPY command writes directly to disk, bypassing the network entirely.

COPY employees
  TO '/tmp/employees_export.csv'
  WITH (FORMAT CSV, HEADER);

3. Exporting Specific Query Results

You rarely need an entire table dump. Both COPY and \copy accept a SELECT statement in parentheses, letting you export filtered, joined, or transformed data directly:

-- Export only active users who signed up in 2023
\copy (
    SELECT name, email, signup_date
    FROM users
    WHERE signup_date >= '2023-01-01'
      AND status = 'active'
)
TO 'active_users_2023.csv'
WITH (FORMAT CSV, HEADER);

This is the most direct way to generate stakeholder reports — write the SQL, pipe the results to a file, done. No intermediate table, no application code.

4. Customizing Your Output Format

PostgreSQL gives you granular control over the output format. The most common customizations:

\copy my_table TO 'data.tsv' WITH (FORMAT CSV, DELIMITER E'\t', HEADER);

\copy my_table TO 'data.csv' WITH (FORMAT CSV, HEADER, NULL 'N/A');

\copy my_table TO 'data.csv' WITH (FORMAT CSV, HEADER, ENCODING 'LATIN1');

5. The pgAdmin GUI Method

If you prefer a visual interface over the command line, pgAdmin 4 provides a built-in export wizard: