Export PostgreSQL query results to CSV correctly

By Anil Gautam · Published and reviewed September 20, 2026

Need a CSV on your computer? Export the query result from your client or use psql’s \copy. With server-side COPY ... TO 'file', the destination is on the database server. That’s an easy detail to miss when you’re working remotely.

Choose what to export

Choose the columns you need, make the filter clear and use a stable sort order. This illustrative query assumes an orders table; replace its identifiers with your own.

SELECT id, customer_id, created_at
FROM public.orders
WHERE created_at >= DATE '2026-01-01'
ORDER BY id;

Run a small preview first. Confirm timezone interpretation, sensitive columns and the recipient's needs. The full export should use the intended filter without accidentally retaining a preview LIMIT.

Know where COPY writes

Server-side COPY uses the server's filesystem and permissions. In psql, the following single-line command sends output to a local file:

\copy (SELECT id, customer_id, created_at FROM public.orders ORDER BY id) TO 'orders.csv' WITH (FORMAT csv, HEADER true)

This is a psql command, not SQL to paste into every GUI. PostgreSQL documents COPY and psql's client-side commands separately.

Check how much your GUI exported

In plainDB, Mac table export can fetch all matching rows. Windows table export can fetch all rows matching the current filters and sort order. Query-result exports use fetched results on both platforms. Check the row count and any fetch limit before treating a file as complete. A 200-row grid and a 50,000-row table are different export scopes.

Check the CSV in the app that will use it

Open the file using an import flow that lets you select column types. Preserve identifiers with leading zeroes as text and check long integers before a spreadsheet rounds them. Decide how NULL and empty strings should be represented and interpreted. Do not validate only by counting newline characters: a quoted CSV field can contain newlines.

For data originating from untrusted users, consider spreadsheet formula interpretation before opening the export. Keep an unchanged source extract and apply presentation-specific handling to the copy intended for a spreadsheet.

A CSV omits much of the database's schema and behavior. If the goal is recovery, use a database backup and test its restore path rather than relying on an export.

Continue reading

Understand NULL and empty strings · Measure data separately

For the desktop workflow, compare plainDB features on Mac and Windows or download the 14-day trial.