PostgreSQL Export to CSV, JSON, Excel and SQL Dumps

PostgreSQL has two COPY commands that look almost the same. COPY ... TO is SQL: the server runs it and writes the file on the database server's disk. \copy ... TO is a psql command: psql runs the same COPY, streams the rows to your machine and writes the file there. When the database runs on another host, in Docker or in the cloud, \copy is almost always the one you want.

\copy users TO 'users.csv' WITH (FORMAT csv, HEADER)

For backups and moving a database, pg_dump writes SQL or an archive that psql or pg_restore loads back. Every command below was run on PostgreSQL 16.13 and 18.6 against the same sample tables (users, orders, order_items) and behaves the same on both unless a section says otherwise.

COPY TO vs \copy

COPY TO with a file name needs superuser or the pg_write_server_files role, and the path is on the server. A normal role gets this:

ERROR:  permission denied to COPY to a file
DETAIL:  Only roles with privileges of the "pg_write_server_files" role may COPY to a file.
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

Even as superuser, the path must be absolute ("relative path not allowed for COPY to file") and writable by the postgres server process, or you get could not open file "/root/users.csv" for writing: Permission denied. \copy has neither limit, since the file is written by your own psql process.

Both need SELECT on the table. Without it, either one fails with ERROR: permission denied for table users.

COPY TO STDOUT is the middle ground: plain SQL that any role can run, with the rows sent to the client. Redirect it in the shell:

psql -d saas_app -c "COPY users TO STDOUT WITH (FORMAT csv, HEADER)" > users.csv

Export query results to CSV with headers

Put the query in parentheses where the table name goes. HEADER writes the column names as the first line.

Example: shipped orders, newest first.

\copy (SELECT id, status, total_amount, created_at FROM orders WHERE status = 'shipped' ORDER BY created_at DESC) TO 'shipped.csv' WITH (FORMAT csv, HEADER)

\copy must fit on one line, because psql ends the command at the line break. Split the query over two lines and psql reports \copy: parse error at end of line, then sends the second line to the server as SQL and fails again. For a long query, end it with \g and an output format instead, which accepts any number of lines:

SELECT id, status, total_amount
FROM orders
WHERE status = 'shipped'
ORDER BY id
\g (format=csv) shipped.csv

From your own shell, psql --csv prints any query as CSV with a header row, so a redirect is enough:

psql -d saas_app --csv -c "SELECT id, name, country FROM users ORDER BY id" > users.csv

A view cannot be the source of a bare COPY. COPY my_view TO STDOUT fails with cannot copy from view "my_view" and the hint Try the COPY (SELECT ...) TO variant. PostgreSQL 16 says the same for a materialized view; PostgreSQL 18 copies a materialized view directly.

CSV options

These go inside WITH (...) after the file name, for both COPY and \copy:

Example: a semicolon-separated file with every value quoted, the shape many European spreadsheet setups expect.

\copy (SELECT id, name, country FROM users ORDER BY id) TO 'users_eu.csv' WITH (FORMAT csv, HEADER, DELIMITER ';', FORCE_QUOTE *)

TO PROGRAM pipes the output through a shell command, which is the easy way to compress. With COPY the program runs on the server and needs superuser or pg_execute_server_program; with \copy it runs on your machine.

\copy users TO PROGRAM 'gzip > users.csv.gz' WITH (FORMAT csv, HEADER)

Export to JSON

row_to_json turns each row into a JSON object, one per line (NDJSON). json_agg wraps all rows into one JSON array, the shape most APIs and JSON.parse expect.

SELECT row_to_json(u) FROM (SELECT id, name, country FROM users ORDER BY id) u;
SELECT json_agg(u) FROM (SELECT id, name, country FROM users ORDER BY id) u;

Write the result with psql's unaligned, tuples-only output (-At), so nothing but the JSON reaches the file:

psql -d saas_app -At -c "SELECT json_agg(u) FROM (SELECT id, name, country FROM users ORDER BY id) u" -o users.json

Do not send JSON through \copy or COPY in the default text format. It escapes backslashes a second time, so a value with a newline comes out as "line one\\nline two" instead of "line one\nline two", and any Windows path doubles its backslashes. The psql -At route above writes the JSON unchanged.

Two more traps. json_agg over zero rows returns NULL, which writes an empty file; use coalesce(json_agg(u), '[]') to get []. And bigint values above 2^53 lose precision in JavaScript: external_event_id 9223372036854775000 parses back as a different number, so cast such columns to text (external_event_id::text) when the file goes to a browser or Node.

json_build_object('id', id, 'name', name) picks and renames keys when the column names should not be the JSON keys.

Export to Excel

PostgreSQL has no Excel writer, so export CSV and open it in Excel, or import it with Data, From Text/CSV, which lets you set the delimiter and column types. Excel on Windows guesses the encoding, so accented names can come out garbled. Writing a UTF-8 byte order mark before the rows makes Excel read the file as UTF-8:

printf '\xEF\xBB\xBF' > users.csv
psql -d saas_app --csv -c "SELECT id, name, country FROM users ORDER BY id" >> users.csv

Excel also strips leading zeros and turns long numbers such as external_event_id into scientific notation when it opens a CSV directly. Import through From Text/CSV and set those columns to Text to keep them intact.

Dump a whole database with pg_dump

pg_dump writes one database as SQL: the CREATE statements, the rows as COPY blocks and the sequence values. It runs as a separate program, not inside psql.

pg_dump -h localhost -U demo -d saas_app -f saas_app.sql

Load it back into an empty database with psql:

createdb saas_app_copy
psql -d saas_app_copy -f saas_app.sql

Since PostgreSQL 17.6, 16.10 and the other August 2025 minor releases, a plain dump starts with a \restrict <key> line and ends with \unrestrict <key>. psql understands them; a tool that runs the file as raw SQL does not, so strip those two lines or use psql. Pipe through gzip for a smaller file: pg_dump -d saas_app | gzip > saas_app.sql.gz.

Schema only or data only

--schema-only (-s) writes the CREATE statements without rows. --data-only (-a) writes only the rows and the setval calls for sequences, for loading into tables that already exist.

pg_dump -d saas_app --schema-only -f schema.sql
pg_dump -d saas_app --data-only -f data.sql

PostgreSQL 18 can also dump planner statistics with --statistics, so a restored database plans queries well before its first ANALYZE. It is off by default, and --statistics-only writes nothing else.

Dump a single table

-t picks a table, and you can repeat it or use a pattern. -T does the opposite and leaves a table out.

pg_dump -d saas_app -t orders -f orders.sql
pg_dump -d saas_app -t 'order*' -f orders_and_items.sql
pg_dump -d saas_app -T audit_log_v2 -f without_audit.sql

A name that matches nothing fails with pg_dump: error: no matching tables were found. -t dumps the table, its indexes, constraints and triggers, but not the functions a trigger calls or the tables a foreign key points to. Loading -t orders into an empty database fails at the foreign key with ERROR: relation "public.users" does not exist, and -t users fails at its trigger with function public.set_updated_at() does not exist.

Custom format and pg_restore

-Fc writes a compressed archive instead of SQL. It is smaller (63 KB against 114 KB for the sample database on PostgreSQL 18) and lets pg_restore pick tables, reorder work and restore in parallel.

pg_dump -d saas_app -Fc -f saas_app.dump
pg_restore -l saas_app.dump

Example: restore the archive into a new database, then restore one table from it into another.

createdb saas_app_restore
pg_restore -d saas_app_restore saas_app.dump

createdb orders_only
pg_restore -d orders_only -n public -t orders saas_app.dump

pg_restore -t matches the table name in every schema, so add -n when two schemas both have an orders. It restores the table, its check constraints and its rows, but not its primary key, indexes or foreign keys; restore the whole archive, or dump with pg_dump -t, when you need those.

Restoring over a database that already has the objects fails on the first one that exists, for example ERROR: function "set_updated_at" already exists with same argument types. Add --clean --if-exists to drop each object before recreating it, or -C -d postgres to create the database named in the dump first.

The two tools do not read each other's files. psql given an archive says The input is a PostgreSQL custom-format dump. Use the pg_restore command-line client to restore this dump to a database. pg_restore given SQL says pg_restore: error: input file appears to be a text format dump. Please use psql.

For big databases, the directory format dumps and restores several tables at once. -j works only with it; pg_dump -Fc -j 4 fails with parallel backup only supported by the directory format.

pg_dump -d saas_app -Fd -j 4 -f saas_app_dir
pg_restore -d saas_app_restore -j 4 saas_app_dir

Extensions are recorded as CREATE EXTENSION, not copied. Restoring onto a server without the extension installed fails with ERROR: extension "vector" is not available, followed by an error for every table that uses its types.

Export as INSERT statements

By default the rows go out as COPY blocks, which only PostgreSQL loads. --inserts writes one INSERT per row, and --column-inserts names the columns too, which survives a different column order and loads into other databases more easily.

pg_dump -d saas_app -t users --data-only --column-inserts -f users_inserts.sql

Each line then looks like this:

INSERT INTO public.users (id, name, email, avatar_url, role, status, country, created_at, updated_at) VALUES (1, 'Mohammad Crist', '[email protected]', 'https://...', 'user', 'active', 'DE', '2024-08-28 08:25:58.535+00', '2025-10-26 23:33:05.505+00');

INSERT dumps restore far slower than COPY. --rows-per-insert=100 batches rows into multi-row INSERTs, which cut the 120 users rows to 2 statements.

Roles and permissions

pg_dump never writes roles, because roles belong to the whole server, not one database. pg_dumpall --globals-only writes the roles (with password hashes) and tablespaces, and --roles-only writes just the roles.

pg_dumpall -U demo --globals-only -f globals.sql
psql -d postgres -f globals.sql

Restore the globals before the databases, or every ALTER ... OWNER TO in the dump fails with an error such as ERROR: role "app_owner" does not exist. To restore without the original owners and grants, dump with --no-owner --no-privileges.

pg_dump needs SELECT on every table and sequence it dumps. A read-only role without it stops at the first one:

pg_dump: error: query failed: ERROR:  permission denied for table products
pg_dump: detail: Query was: LOCK TABLE public.users, public.products, ...

Granting SELECT on the table is not enough for -t users, since its serial column has a sequence. PostgreSQL 16 reports permission denied for sequence users_id_seq; PostgreSQL 18 says failed to get data for sequence "users_id_seq"; user may lack SELECT privilege on the sequence. Grant both, or give the role pg_read_all_data:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO reporting;

pg_dump version mismatch

pg_dump must be the same major version as the server or newer. Version 16 against a PostgreSQL 18 server refuses to run:

pg_dump: error: aborting because of server version mismatch
pg_dump: detail: server version: 18.6 (Debian 18.6-1.pgdg13+2); pg_dump version: 16.13 (Debian 16.13-1.pgdg12+1)

Install the newer client, or run pg_dump inside the server's own container. Going down a version is where the trouble is: pg_restore 16 cannot read an archive written by pg_dump 18 (pg_restore: error: unsupported version (1.16) in file header). A plain SQL dump from pg_dump 18 loads into 16, though its SET transaction_timeout = 0 line fails with unrecognized configuration parameter "transaction_timeout", which is harmless.

From a Docker container

When PostgreSQL runs in Docker, run pg_dump inside the container and redirect its output to a file on your machine. The container's pg_dump always matches the server version.

docker exec my-postgres pg_dump -U demo -d saas_app -Fc > saas_app.dump
docker exec -i my-postgres pg_restore -U demo -d saas_app_restore < saas_app.dump

Leave out -t. With a terminal attached, Docker turns line endings into \r\n and corrupts a binary archive, and restoring it then fails with pg_restore: error: could not read from input file: end of file. The same docker exec pattern works for CSV: docker exec my-postgres psql -U demo -d saas_app -c "\copy users TO STDOUT CSV HEADER" > users.csv.