PSQL not executing SQL command - Powershell

I have a simple script that should execute a select statement and output it to a CSV. My problem is that the script connects to the database and hangs waiting for my sql instructions (which are on the script and should execute automatically). Any help would be greatly appreciated.

Set-Location 'E:\Program Files\PostgreSQL\9.1\bin\';
$env:PGPASSWORD = 'mypwd';
.\psql --% -U postgres -w myDB 
.\psql --% -c "copy {'SELECT * FROM myTable';} TO    'C:\Users\e\Desktop\test1.csv' CSV DELIMITER ',';"

Set-Location 'E:\Program Files\PostgreSQL\9.1\bin\';
$env:PGPASSWORD = 'mypwd';
.\psql --% -U postgres -w myDB 
.\psql --% -c "copy {'SELECT * FROM myTable';} TO    'C:\Users\e\Desktop\test1.csv' CSV DELIMITER ',';"

You invoke psql twice here. The first enters interctive mode as no stdin was supplied, no -f filename parameter was supplied, and there's no -c command string.

So the script never gets to the second psql invocation. Put the parameters on one line.

Additionally:

• You want WITH (CSV, HEADER) not CSV DELIMITER ','

• {'SELECT FROM myTable';} is complete nonsense. Simply use COPY mytable TO ... or if you need a subquery, use COPY (SELECT FROM mytable) TO ...

• If you're trying to do a line continuation, use a backtick at the end of the line.






• Unless you intend to use server-side paths, with the file permissions of the PostgreSQL server, you probably want \copy not COPY




(I'm assuming that --%` stops Powershell from looking for Powershell flags and options in the rest of the command; I haven't seen it before).

I think you're looking for something more like:

Set-Location 'E:\Program Files\PostgreSQL\9.1\bin\';
$env:PGPASSWORD = 'mypwd';
.\psql --% -U postgres -w myDB -c "\copy (SELECT * FROM myTable) TO    'C:\Users\e\Desktop\test1.csv' WITH (FORMAT CSV, HEADER);"

StackExchange Database Administrators Q#110699, answer score: 6