Exporting a PostgreSQL database is a common operation: a one-off backup, a transfer to another tool, analytical archiving, or simply sharing a dataset. PostgreSQL does not offer a single export format, and that is for the best: CSV, JSON, and Parquet address three distinct needs, with different tools and costs.
This guide reviews the native PostgreSQL commands for each format, the third-party tools that automate the task, and the criteria for choosing the right format depending on context.
Exporting to CSV with COPY
The native method for producing a CSV from PostgreSQL is the COPY command. It runs server-side, reads the table directly from the engine, and writes the result to a file. It is the fastest and most memory-efficient approach, because it avoids the client/server round trip.
The basic syntax is COPY (SELECT * FROM users) TO '/tmp/users.csv' WITH (FORMAT CSV, HEADER). The file is written server-side, which assumes access to the database's file system. To export from a remote client, you use \copy in psql instead, which runs the same logic but writes to the client machine.
COPY accepts a custom delimiter, quoting, an encoding, and an alternative format (TEXT, BINARY). For bulk exports, it is the right choice: on tables of several million rows, it stays linear in server memory as long as the SELECT itself doesn't aggregate everything in RAM.
Exporting to JSON with row_to_json
PostgreSQL has handled JSON natively since version 9.2. For a JSON export, two approaches dominate. The first uses row_to_json or json_agg directly in SQL, which produces a valid JSON file in a single command.
A typical example: COPY (SELECT json_agg(row_to_json(t)) FROM users t) TO '/tmp/users.json'. This command produces a single JSON array. For very large tables, it is better to avoid json_agg, which materializes everything in server memory. Instead, you write JSONL (one object per line) with COPY (SELECT row_to_json(t) FROM users t) TO '/tmp/users.jsonl', a format that most analytical pipelines know how to read.
The second approach combines psql and jq to post-process a text output. This is useful when you want to filter or reshape the records on the client side, but it is less performant than the native JSON functions.
Exporting to Parquet with pg_parquet or DuckDB
Parquet is a compressed columnar format, designed for analytics. It is not supported natively by PostgreSQL. Two solutions exist. The first is the pg_parquet extension (maintained by Crunchy Data), which adds a COPY ... TO '...' (FORMAT PARQUET) syntax directly into the engine. It requires installation privileges on the database.
The second solution is DuckDB. You use it as an intermediate engine: DuckDB connects to PostgreSQL through its postgres_scanner extension, reads the table remotely, and writes the Parquet locally with COPY (SELECT * FROM pg.users) TO 'users.parquet' (FORMAT PARQUET). No need to modify the source database. It is the most flexible approach when you don't have control over PostgreSQL.
Parquet compresses repetitive columns heavily (typically 5 to 10x less volume than an equivalent CSV) and preserves types. It is the reference format for analytical archiving or loading into a data warehouse.
Which format for which use
CSV remains the lingua franca of tabular exchange. Excel, Sheets, R, Pandas, all the classic ETL tools read it. It doesn't handle types or nested structures, but its simplicity remains an asset for sharing an extract with a non-technical colleague or feeding a spreadsheet.
JSON is called for when the data contains JSONB, nested objects, or variable-length fields. It preserves the structure, at the cost of a noticeably larger volume. JSONL (one object per line) is preferable to a JSON array as soon as you go beyond a few hundred thousand rows.
Parquet stands out for analytics: large volumes, columnar queries, loading into BigQuery, Snowflake, Athena, DuckDB. It preserves numeric, date, and boolean types, and stays readable years later without depending on a local encoding.
Exporting from a GUI client
The native commands are efficient but require shell access and knowledge of the options. For a regular workflow, a GUI client avoids the back and forth. DBeaver offers an export wizard to CSV, JSON, XML, and SQL. DataGrip does the same. pgAdmin is limited to CSV and the PostgreSQL binary format.
QoreDB exports to CSV, JSON, SQL, HTML, XLSX, and Parquet from any query result. The export goes through a streaming pipeline on the Rust side: the query is read row by row via a cursor, serialized on the fly by a writer dedicated to each format, then written to disk. Memory stays bounded even on exports of several million rows.
The Parquet writer uses the official Arrow and Parquet crates, with a row group size of 10,000 rows and automatic type mapping (int, float, bool, binary, string). Long exports go through the Background Job Manager: the user can start several exports in parallel, track their progress, and keep working.
Choosing your approach depending on context
For a one-off, scriptable export, COPY on the command line is unbeatable. For a recurring workflow where you want to filter a result, preview it, and export it in several formats, a GUI client saves time. For Parquet without installing a server-side extension, DuckDB running locally is the best bridge.
Choosing the format comes down to three questions: who consumes the file, what volume, and what lifespan. CSV for humans and spreadsheets, JSON for complex structures, Parquet for long-term analytics. With today's tools, producing all three from PostgreSQL is a matter of minutes, not hours.
Stay updated on new releases
Subscribe to get product releases, new drivers notifications, and technical tutorials.