Export PostgreSQL to CSV

Export a PostgreSQL table or query to CSV.

There are two commands that look almost identical and behave completely differently. Knowing which one you want takes about a minute and saves the permission error that brings most people to this page.

Short answer: Use \copy in psql to write a CSV to your own machine — it needs no special privileges. Use COPY only when you want the file written on the database server, which requires superuser or the pg_write_server_files role. A GUI client exports the grid directly, which is usually the fastest route for a one-off.

Download PgDeck View pricing

PgDeck PostgreSQL desktop client with schema navigation, table filtering, row editing, and SQL tools

Why developers use PgDeck

PgDeck is shaped around PostgreSQL tasks that happen every day: connect, inspect, filter, edit, query, manage structure, copy, and export.

\copy writes on your machine; COPY writes on the server

COPY needs superuser or the pg_write_server_files role

NULL exports as an empty field unless you say otherwise

Excel needs a BOM to read UTF-8 CSV correctly

COPY TO STDOUT lets you pipe the output anywhere

Secure connections

Save PostgreSQL connection details with encrypted passwords, SSL settings, URL import, testing, search, edit, and delete actions.

Schema and data grid

Browse schemas and tables, search the table list, pin favorites, filter rows, sort columns, resize columns, and paginate results.

SQL workspace

Run SELECT and non-SELECT statements from multi-tab SQL editors with syntax highlighting and interactive query results.

Staged editing

Edit cells, add rows, duplicate rows, set NULL, stage deletes, review pending changes, and commit everything in one transaction.

How to export PostgreSQL data to CSV

1
Export to your own machine with \copy
\copy is a psql meta-command. It runs the query on the server but writes the file locally through your existing connection, so it needs no file permissions on the database host. This is what you want almost every time. Note there is no semicolon — it is a psql command, not SQL.
```
\copy (SELECT id, email, created_at FROM users WHERE active) TO 'users.csv' WITH (FORMAT csv, HEADER)
```
2
Use COPY only for files on the server
COPY is real SQL and writes to the database server's filesystem, not yours. It needs superuser or membership of pg_write_server_files. If you are getting "must be superuser or a member of the pg_write_server_files role", or "could not open file for writing: Permission denied", you wanted \copy.
```
COPY (SELECT * FROM users) TO '/var/lib/postgresql/users.csv' WITH (FORMAT csv, HEADER);
```
3
Decide how NULL should look
By default a NULL becomes an empty field, which makes it indistinguishable from an empty string when the file is read back. If that distinction matters to whoever receives the file, set it explicitly.
```
\copy users TO 'users.csv' WITH (FORMAT csv, HEADER, NULL '\N')
```
4
Fix the Excel encoding problem
Excel on Windows opens UTF-8 CSV files as the system codepage unless the file starts with a byte order mark, which turns accented characters into mojibake. PostgreSQL will not add a BOM. Either add one afterwards, or export as UTF-16 if the recipient only ever opens it in Excel.
```
# Add a BOM so Excel reads UTF-8 correctly
printf '\xEF\xBB\xBF' > users-excel.csv
cat users.csv >> users-excel.csv
```
Or export from a GUI client
For a one-off export, filtering the table in a client and exporting the result is faster than writing the command. PgDeck exports the current filtered view or a query result to CSV, JSON, or SQL INSERT statements, so the rows you exported are exactly the rows you were looking at.

COPY versus \copy, once and for all

COPY is SQL executed by the server, so its file paths are server paths and it needs server file permissions. \copy is a psql client command that runs the same query but streams the result back over your connection and writes it locally. On a managed database like RDS, Supabase, or Neon you have no filesystem access at all, so \copy is the only one of the two that can work.

Export a query, not just a table

Both commands accept a full query in parentheses, which means you can filter, join, order, and rename columns before the data ever reaches the file. That is almost always better than exporting an entire table and cleaning it up in a spreadsheet, and it keeps the export reproducible — the query is the record of what you sent.

Piping with STDOUT

COPY ... TO STDOUT writes to standard output instead of a file, so you can compress on the fly, send it straight into another program, or stream it over a pipe. Useful for large exports where writing an intermediate file is wasted disk and time.

Large exports need a little care

A multi-gigabyte CSV will take a while and hold a transaction open, which matters on a busy database because it can delay autovacuum. Run large exports against a replica if you have one, or during quiet hours. Compressing as you go with a STDOUT pipe usually costs less time than it saves in I/O.

The columns you export are a decision

SELECT * on a users table puts password hashes, tokens, and email addresses into a file that will be attached to an email and forgotten in a downloads folder. Name the columns you actually need. This is the single most common way personal data escapes a database, and it takes ten extra seconds to avoid.

How do I export a PostgreSQL table to CSV?

In psql, use \copy tablename TO 'file.csv' WITH (FORMAT csv, HEADER). This writes the file to your own machine and needs no special privileges. You can also pass a full query in parentheses instead of a table name to filter first.

What is the difference between COPY and \copy?

COPY is SQL run by the server and writes to the server's filesystem, requiring superuser or the pg_write_server_files role. \copy is a psql client command that writes to your local machine over the existing connection and needs no extra privileges. On managed databases only \copy works.

Why does COPY say permission denied?

Because COPY writes on the database server, using the server's file permissions and the postgres user's access. Either grant pg_write_server_files, or — far more likely what you want — use \copy instead, which writes locally.

Why do accented characters break when I open the CSV in Excel?

Excel on Windows assumes the system codepage unless a UTF-8 file begins with a byte order mark. PostgreSQL does not add one. Prepend a BOM to the file after exporting, or export as UTF-16 if Excel is the only consumer.

How do I export only some rows to CSV?

Put a query in parentheses instead of a table name: \copy (SELECT id, email FROM users WHERE active) TO 'active.csv' WITH (FORMAT csv, HEADER). In a GUI client, filter the table first and export the filtered view.