Postgres TIL: Multi-Line Copy in psql

Written 2026-07-08

Today I learned about a better way to copy query results into a local file from a remote Postgres database using psql. psql has a builtin \copy command for doing this, but it isn’t as useful as you would think due to special parser rules. “Unlike most other meta-commands, the entire remainder of the line is always taken to be the arguments of \copy, and neither variable interpolation nor backquote expansion are performed in the arguments” (psql - Postgres Docs).

The biggest restriction in practice is that you cannot wrap a multi-line query using a \copy. I’ve run into this multiple times when trying to quickly export a query I am working on as a CSV to send to a coworker.

My revelation today comes straight from the same documentation section I quoted above:

Another way to obtain the same result as \copy ... to is to use the SQL COPY ... TO STDOUT command and terminate it with \g filename or \g |program. Unlike \copy, this method allows the command to span multiple lines; also, variable interpolation and backquote expansion can be used.

The PostgreSQL Global Development Group

So, I can quickly wrap my SQL query like this:

Query (tested with psql v14, Postgres v14)

copy (
  select 42 as the_answer
) to stdout with (format csv, header)
\g data.csv

This is a pretty small feature, but it really comes in handy when someone needs some data right away. Knowing the best way to copy data out of Postgres offhand means I spend less time fussing with my tools and more time focusing on writing SQL and helping people.

Update (2026-07-10)

I discovered a better way to do this that allows you to use a query from a file, and pipe the output into another file. You can use \o [FILE] to send query results to a file. I’ve used this command before for storing EXPLAIN ANALYZE output, but I didn’t think to use it for generating CSVs until today. This was really useful for me because it means I can store my query in query.sql and run it straight to a CSV file with this:

\o results.csv
\i query.sql

If you run \? in psql, you will get a full list of other useful commands.