How to Export a Table to CSV in PostgreSQL

How do I export a PostgreSQL table to CSV with the COPY command?

Use COPY or \copy to write a PostgreSQL table (or query result) to a CSV file with optional header, delimiter, and encoding settings.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

How do I export a PostgreSQL table to CSV from SQL?

Run COPY table_name TO '/absolute/path/file.csv' WITH (FORMAT csv, HEADER true); inside psql or any SQL client. PostgreSQL streams the table to the file on the server.

How do I export when I don't have file-system access?

Use the psql meta-command \copy. It sends the data through the client connection and writes to a file on your local machine, avoiding server-side file permissions.

Which COPY options matter most?

FORMAT csv enables CSV output. HEADER true writes column names. DELIMITER ',', QUOTE '"', and ENCODING 'UTF8' let you match downstream requirements.

Best practice: qualify columns with a SELECT

Instead of dumping the whole table, use COPY (SELECT col1, col2 FROM table_name WHERE ... ) TO ... to apply filters or ordering.

How can I export from a remote host over SSH?

Combine psql and \copy in an SSH session or direct the output to STDOUT and pipe it: psql -c "COPY mytab TO STDOUT WITH (FORMAT csv, HEADER)" | gzip > mytab.csv.gz.

How do I include NULLs as empty fields?

Default CSV output writes nothing between delimiters for NULL. To change, CAST or COALESCE in a SELECT inside COPY.

Can I export multiple tables at once?

Run separate COPY/\copy commands or script them with psql. Each table needs its own target file.

Security tip

Grant pg_read_server_files (or superuser) for server-side COPY. Prefer \copy for least-privilege workflows.

Why How to Export a Table to CSV in PostgreSQL is important

How to Export a Table to CSV in PostgreSQL Example Usage


\copy (SELECT id, email FROM users WHERE active) TO 'active_users.csv' WITH (FORMAT csv, HEADER, ENCODING 'UTF8');

How to Export a Table to CSV in PostgreSQL Syntax


-- Server-side (requires file-system access)
COPY [TABLE] table_name [ (column_list) ]
     TO 'absolute/path/file.csv'
     [ WITH (FORMAT csv
             [, HEADER {true|false}]
             [, DELIMITER 'char']
             [, QUOTE 'char']
             [, NULL 'string']
             [, ENCODING 'name'] ) ];

-- Client-side (psql only)
\copy [TABLE] table_name [ (column_list) ] TO 'relative/or/full/path/file.csv' [ (FORMAT csv, HEADER) ]

Common Mistakes

Using COPY instead of \copy when you lack server-side permissions. Result: "Could not open file for writing." Fix: switch to \copy so the file is created locally.
Forgetting HEADER true, leading to missing column names in the first row. Add HEADER true in the WITH clause to write headers.

Frequently Asked Questions (FAQs)

Does COPY overwrite existing files?

Yes. If the file already exists and the PostgreSQL server can write to it, COPY truncates and overwrites it. Use a new filename to avoid accidental loss.