Exporting a PostgreSQL table via command line: how to escape double quotes

Question

I'm trying to use the psql command line on a Windows 11 machine to export the results of a query from a PostgreSQL 15 database to a CSV file.

The problem I am facing is that the query in question is this:

SELECT * FROM "MyTable"

but while this works fine when run via pgAdmin, if I run the following command line (connection parameters omitted for brevity):

./psql.exe -c "COPY (SELECT * FROM MyTable) TO 'c:/out.csv'"

I get an error saying:

ERROR: relationship "mytable" does not exist
ROW 1: COPY (SELECT * FROM MyTable) TO 'c:/out.csv'

I suspect that the problem might be that I'm not using the double quotes " around the table name, because if I run the query in pgAdmin without the double quotes I get the exact same error. But how do I put the double quotes in the command line? In the windows cmd/powershell command line the double quotes are argument value delimiters, so if I use them INSIDE the argument value it just truncates it.

I've tried using double quotes "" or the tick `" to escape it following some suggestions I've found online, but the error message never changes. What am I doing wrong here?

PS: I know I can launch PSQL in interactive shell mode and issue the command from there, but this is no good to me because this command is part of a script, I don't want it to be interactive, I want it to just execute and return to the command line.

Answer 1

You should be able to swap the internal single quotes ' for double-dollar quotes $$, which releases the string constant single quotes ' to be used as outer quotes, and double-quotes for delimited identifier ", which can simplify things.

In PowerShell you won't need the backtick ` to protect the $ because single-quoted strings are verbatim, already preventing their evaluation. As pointed out by @mklement0 below, what you will need in PS version <7.3.x is a backslash to let the parameter hold onto the double-quotes:

./psql.exe -c 'COPY \"MyTable\" TO $a$c:/out.csv$a$ CSV HEADER'

As an alternative to $$, you can double the single quotes around file name:

./psql.exe -c 'COPY \"MyTable\" TO ''c:/out.csv'' CSV HEADER'

Double-double-quoting should also work, but the backslash requirement complicates it a bit:

./psql.exe -c "COPY \""MyTable\"" TO 'c:/out.csv' CSV HEADER"

Note that regular COPY happens server-side. psql offers client-side meta-command \copy:

./psql.exe -c "\copy \""MyTable\"" TO 'c:/out.csv' CSV HEADER"

or trade psql for pg_dump, if you're fine with a .sql or custom dump instead of a .csv

./pg_dump.exe -t '\"MyTable\"' -f 'c:/MyTable_dump'

In cmd, single outer quotes won't work, but double quotes on the inside work fine:

./psql.exe -c "\copy ""MyTable"" TO 'c:/out.csv' CSV HEADER"

If you're copying all columns and rows of the entire table, you can give both versions of copy its name directly, instead of the (select * from "MyTable") or (table "MyTable"). Parenthesized syntax is more useful for queries with some additional complexity.