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.
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.